Del first row of text out of two rows in cell

Closed
hash - Oct 29, 2010 at 10:40 AM
RayH
Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016
- Oct 31, 2010 at 06:00 PM
Hello,

I am in a great problem and my boss has given me a assignment with lots of worksheets of excel. These worksheets has lots of cells in it. The example is as shown

?HRSG2?????1???A
Open HRSG2 HPSH 1 drain MV A

Now you consider it as a cell. In each cell there are two rows of text, first row in a cell is in chinees language and second row of text in a cell is in english language.

Now the assignment is to remove the Chinees text that is in first row of cell and to leave is only english text. Number of letter in each row of cell is maximum 20 letters

I have lot of cells like this. i can do it manually but it take too time to do that please help me to remove the first row of text in a cell and give me a formula to apply on all the cells

Regards
Hashim Bukhari

3 replies

RayH
Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016
25
Oct 29, 2010 at 07:20 PM
In a new cell:
=RIGHT(A3,LEN(A3)-SEARCH("LF",A3,1))

Notice in the formula "LF". This needs to be replaced by the code for LineFeed which is 010. To type this in the formula bar press the ALT key and type 010 on the keypad. If you've typed this in correctly you'll see the formula is displayed as:
=RIGHT(A3,LEN(A3)-SEARCH("
",A3,1))

You will probably need to drag down the formula bar to see it as you normally only see one line.

What this is doing is looking for a lifefeed character in the cell, and returning is position in the string. Then, using this to find the remaining characters.
0
I have tried this but what he has done is

First it was like this and ? mark indicates the chinees language

?HRSG2?????1???A
Open HRSG2 HPSH 1 drain MV A

and after applying the formula it is converted into this

?HRSG2?????1???A Open HRSG2 HPSH 1 drain MV A

Now what i want is to remove the Upper line in a cell that is in chinees

Please help :(
0
RayH
Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016
25
Oct 31, 2010 at 06:00 PM
Well this odd because I cannot replicate what I did earlier.
So, instead on pressing Alt-010 to replace LF press Alt Enter. This will insert the linefeed character instead. And is a much better solution and will give you what you need.
0