get substring

  • Thread starter Thread starter moonhkt
  • Start date Start date
M

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
 
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 ?
Click to expand...

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
 
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>
Click to expand...

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
 
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
Click to expand...


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))
 
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))
Click to expand...

Add another '-1' as follows...

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