Separate text from numbers in a string?

[LoriD]

Is there a way to separate text from numbers, (other than text to columns)
My data is all different lengths, nothing consistent,plus I want to put the separated data in another column.
I'm hoping there is a formula???
Thanks in advance for your help.

 

[thomas_lingsley]

What if I have the text and digits arranged in the opposite fashion?

0025grams --> 25 grams
0350milligrams --> 350 milligrams
1.75grams --> 1.75 grams

Ultimately I would like 25 and grams to be in separate cells. Is there a way to do this?

 

[Aladin Akyurek]

What if I have the text and digits arranged in the opposite fashion?

0025grams --> 25 grams
0350milligrams --> 350 milligrams
1.75grams --> 1.75 grams

Ultimately I would like 25 and grams to be in separate cells. Is there a way to do this?

Try...

B2, copy down:

=LOOKUP(9.99999999999999E+307,--LEFT(A2,ROW(INDIRECT("1:"&LEN(A2)))))

C2, copy down:

=REPLACE(A2,1,FIND(B2,A2)+LEN(B2)-1,"")

 

[thomas_lingsley]

Wow! This is great! You ROCK!

Thank you!
Thomas

 

[aaugust2]

I was wondering if anyone knew how to separate a cell like this:

825510305-SOFTWAR

I needed to have 2 additional cells, one containing just the numbers and one containing just the text.

Thank you!

 

[hiker95]

aaugust2,

Welcome to the Excel forum.

Excel Workbook
	A	B	C
1	825510305-SOFTWAR	825510305	SOFTWAR
Sheet1

 

[Peter_SSs]

aaugust2

Welcome to MrExcel!

For hiker95's setup, I would suggest this alternative single function formula for C1:
=SUBSTITUTE(A1,B1&"-","")

Note also that you do not need formulas to do this task - you could do a whole column at once quite quickly. Investigate 'Text to Columns' and use 'Delimited' with '-' as the delimiter. If you need more help with this method, please advise which Excel version you are using.

 

[aaugust2]

I was wondering if someone knew how to separate something like this: C247-BUILDING. Obviously to the left would be C247 and to the right would be BUILDING. I was using a formula that would extract numbers from the left side where C247 is, but when this combination popped up I kept getting an error message.

Thanks!

 

[Peter_SSs]

I was wondering if someone knew how to separate something like this: C247-BUILDING. Obviously to the left would be C247 and to the right would be BUILDING. I was using a formula that would extract numbers from the left side where C247 is, but when this combination popped up I kept getting an error message.

Thanks!

Just leave the VALUE() function out of the previously suggested formula. VALUE() is trying to force the answer to be a number, which it clearly cannot be with a letter in it.

Excel Workbook
	A	B	C
1	C247-BUILDING	C247	BUILDING
Separate Text

But don't forget my earlier comment:

Note also that you do not need formulas to do this task - you could do a whole column at once quite quickly. Investigate 'Text to Columns' and use 'Delimited' with '-' as the delimiter. If you need more help with this method, please advise which Excel version you are using.

 

[CHINH CK]

Hi,
how about my case as:
<TABLE style="WIDTH: 377pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=503><COLGROUP><COL style="WIDTH: 179pt; mso-width-source: userset; mso-width-alt: 8740" width=239><COL style="WIDTH: 150pt; mso-width-source: userset; mso-width-alt: 7314" width=200><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 179pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=20 width=239>A</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 150pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 width=200>B</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 width=64>C</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl67 height=20>Hop KC lon mut dua sua 200grx12Hop</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl67>Hop KC lon mut dua sua</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl68 align=right>200</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63 height=20>Hop KC lon-mut gung deo 200grx12Hop</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63>Hop KC lon-mut gung deo</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 align=right>200</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63 height=20>Hop mut tet thap cam vuong 550g x 6 hop</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63>Hop mut tet thap cam vuong</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 align=right>550</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63 height=20>Hop mut tet thap cam chu nhat 510gx6hop</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63>Hop mut tet thap cam chu nhat</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 align=right>510</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63 height=20>Hop mut tet bi mass (450g) x 10 hop</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63>Hop mut tet bi mass (</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 align=right>450</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63 height=20>Hop KC lon-mut mang cau 250grx12Hop</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63>Hop KC lon-mut mang cau</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 align=right>250</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63 height=20>Hop KC lon-mut khoai lang 300grx12Hop</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63>Hop KC lon-mut khoai lang</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 align=right>300</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63 height=20>Mut TC hop chiec la 350grx10Hop</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63>Mut TC hop chiec la</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 align=right>350</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63 height=20>Mut TC HN Oval loc xuan 380grx6Hop</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63>Mut TC HN Oval loc xuan</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 align=right>380</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63 height=20>Mut TC HN vuong loc xuan 465gx6Hop</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63>Mut TC HN vuong loc xuan</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 align=right>465</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63 height=20>Hop mut tet gung mass (400g) x 10 hop</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63>Hop mut tet gung mass (</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 align=right>400</TD></TR></TBODY></TABLE>

what formula should be used so from A i can get result as B and C.
Plz plz plz plz help help help :D
sorry, very newbie in this. :d

 

[Peter_SSs]

Welcome to the MrExcel board!

Try these formulas, copied down.

Excel Workbook
	A	B	C
1
2	Hop KC lon mut dua sua 200grx12Hop	Hop KC lon mut dua sua	200
3	Hop KC lon-mut gung deo 200grx12Hop	Hop KC lon-mut gung deo	200
4	Hop mut tet thap cam vuong 550g x 6 hop	Hop mut tet thap cam vuong	550
5	Hop mut tet thap cam chu nhat 510gx6hop	Hop mut tet thap cam chu nhat	510
6	Hop mut tet bi mass (450g) x 10 hop	Hop mut tet bi mass (	450
7	Hop KC lon-mut mang cau 250grx12Hop	Hop KC lon-mut mang cau	250
8	Hop KC lon-mut khoai lang 300grx12Hop	Hop KC lon-mut khoai lang	300
9	Mut TC hop chiec la 350grx10Hop	Mut TC hop chiec la	350
10	Mut TC HN Oval loc xuan 380grx6Hop	Mut TC HN Oval loc xuan	380
11	Mut TC HN vuong loc xuan 465gx6Hop	Mut TC HN vuong loc xuan	465
12	Hop mut tet gung mass (400g) x 10 hop	Hop mut tet gung mass (	400
13
Split Text