get substring

[moonhkt]

Hi All

Version : Excel 2003

Cell value B9 is 123 + alt Enter + ABC + alt Enter + 789

How to get 123 in cell B10 and ABC in cell B11 ?

Try to using "|" replace with chr(10) no work.
=LEFT(B9,SEARCH("|",B9)-1)


moonhkt

 

[Claus Busch]

Hi moonhkt,

Am Tue, 26 Apr 2011 07:47:09 -0700 (PDT) schrieb moonhkt:

Cell value B9 is 123 + alt Enter + ABC + alt Enter + 789

How to get 123 in cell B10 and ABC in cell B11 ?

B10: =LEFT(B9,FIND(CHAR(10),B9)-1)
B11:
=RIGHT(LEFT(B9,FIND("#",SUBSTITUTE(B9,CHAR(10),"#",2))-1),FIND(CHAR(10),B9))



Regards
Claus Busch

 

[Gord Dibben]

Select B9

Data/Text to Columns
Delimited
<next>
select Other and with the cursor in the blank box hold down the <alt> key and type 010 on the **Numeric key pad**.
<finish>

Extra info only.................

CTRL + j is same as Alt + 010 and you don't have to remember the numpad<g>


Gord Dibben MS Excel MVP

 

[moonhkt]

Extra info only.................

CTRL + j  is same as Alt + 010 and you don't have to remember the numpad<g>

Gord Dibben     MS Excel MVP

Thank a lot.
This formula is not work. What is "#" ? I will check on the formula.
=RIGHT(LEFT(B9,FIND("#",SUBSTITUTE(B9,CHAR(10),"#",
2))-1),FIND(CHAR(10),B9))

 

[GS]

moonhkt used his keyboard to write :

Thank a lot.
This formula is not work. What is "#" ? I will check on the formula.
=RIGHT(LEFT(B9,FIND("#",SUBSTITUTE(B9,CHAR(10),"#",
2))-1),FIND(CHAR(10),B9))

Add another '-1' as follows...

=RIGHT(LEFT(B9,FIND("#",SUBSTITUTE(B9,CHAR(10),"#",2))-1),FIND(CHAR(10),B9)-1)