Some hidden text but useful-Trouble editing

[Ehtisham Ali]

Hi Guys,

I have a excel file in which their is some text which I use as a command to configure routers.

In row 10 the text contains some IP which contains some text which I am unable to edit.

If I use Clean() on the text it becomes editable, but unusable as a command.

Please check once. It is really mess for me.

Please help guys.

 

[Hui]

Rows 10-15 have the Non-Printable character 30 as the first character
You can see this if in B9: =RIGHT(A9,LEN(A9)-1)
Copy down
Note how the X is visible on Xtelnet but the T is missing on Telnet
The formula has removed the left most character

So you can fix All the Rows by using
B1: =IF(CLEAN(A1)=A1,A1,RIGHT(A1,LEN(A1)-1))
Copy down

ps: Ascii code 30 is the Record Separator character if that means anything in the context of the data

 

[Ehtisham Ali]

Rows 10-15 have the Non-Printable character 30 as the first character
You can see this if in B9: =RIGHT(A9,LEN(A9)-1)
Copy down
Note how the X is visible on Xtelnet but the T is missing on Telnet
The formula has removed the left most character

So you can fix All the Rows by using
B1: =IF(CLEAN(A1)=A1,A1,RIGHT(A1,LEN(A1)-1))
Copy down

ps: Ascii code 30 is the Record Separator character if that means anything in the context of the data

Thanks for your quick response. I will try your solution and see if works as a command then. I will update the result accordingly