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.

 

[John Davis]

Hi Peter,
Thank you so much! C2 formula works beautifully.
BTW, I am thinking to start learning some programing in VBA. Can you recommend some quick starter's book? Say some books would help me program the process above into a VBA code?

Have a good day!
Li

Sorry too intrude. But have you ever tried using the Macro Recorder?

 

[lydiainflorida]

Sorry to hijack the thread but I have a similar question.

How do I separate the following string Q41000BL00 into Q41000 and BL00?

The file has 5000 records and each one contains the product number in the format of item number followed by the color code. Q41000 is the item number and BL00 is the color code. Some color codes are strictly text and some has numbers after the color code (like the BL00). Therefore, some product numbers look like this - Q41000BL and some look like this - Q41000BL00.

The only information I need is Q41000. Basically if there's a way to delete anything after Q41000. Not all item numbers has text in it (some has all numbers), but color code always start with text.

Here're some examples:

Q61006YL00
Q62000BL00
Q62000GR00
Q62000RD00
Q62006GR00
Q63000BL00
Q63006BL00
Q64000GR00
Q65000GR00
Q65100BL00
Q80000WH00
Q80500WH00
Q81000YL00
Q82000BL00
616700BLA
617000BLA
617400BLA
617600BLA
9T6300BLA
9T6400BLA
424288BLA
424300BLA
424400BLA
424500BLA
424700BLA
424806BLA
505012
617800BLA
9VBH100000
9VBP060000
9VBP100000
9VCV120000
9VCV160000
9VMH120000
9VMH150000
9VPH120000
9VPH150000
9VUL120000
370612714
370712907
564000BLA
564200BLA
564210BLA
564261BLA
565461BLA
565861BLA
565961BLA
566000BLA
566261BLA
566361BLA
2084300
2084400
2088300
20564020
20566300
22564210
50564205
50565400
50566200
50566300
51566000
52564200
53565800
70565900
925658C1BLA
925659C1BLA
007710-000-000
007750-000-000
007800-000-000
007810-000-000
007820-000-000
007826-000-000
007830-000-000
007842-000-000
007895-000-000
007908-000-000
10
1000EBK
100P00CLR
10100
10100S
101100BLA
101300BLA
101700BLA
101900BLA
101P00CLR
10200
102000BLA
10200S
102100BLA
102200BLA
102300BLA

Thanks!

 

[Peter_SSs]

lydiainflorida

Welcome to the MrExcel board!

A bit more information and example results may help. For example, what results do you expect for these and why?

505012
9VBH100000
007710-000-000
10

 

[lydiainflorida]

lydiainflorida

Welcome to the MrExcel board!

A bit more information and example results may help. For example, what results do you expect for these and why?

505012
9VBH100000
007710-000-000
10

Sorry, in the above cases, there are no texts following a string of numbers. In these cases, there is no need to separate anything.

Here's more detail and background on this project - in the first example I gave Q41000BL00, Q41000 is the item number and BL00 is the color code. I am trying to merge two spreadsheets together. One of them has the item number on its own (Q41000), the other one has the item number with the color code tagged on at the end (Q41000BL00). I need both files to have the same reference point so that I can combine them together.

Not every item has different colors and that's why in the above examples you cited, there are no text following numbers at the end.

Thank you!

 

[l2zhang]

I have some related questions:

Say I have two column of data as shown below:

Column A Column B

<TABLE style="WIDTH: 568pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=758 border=0 x:str><COLGROUP><COL style="WIDTH: 284pt; mso-width-source: userset; mso-width-alt: 13860" span=2 width=379><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 284pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=379 height=18>Construction in Progress / Adjustments 449.4</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 284pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=379 x:fmla='=TRIM(LEFT(A1,FIND(".",A1)+2))'> Construction in Progress / Adjustments 449.4</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18>Property, Plant, & Equipment 6,265.2 </TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:fmla='=TRIM(LEFT(A2,FIND(".",A2)+2))'> Property, Plant, & Equipment 6,265.2</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18>Total Property, Plant, & Equipment 6,714.7 3,791.4 46.6 2,876.7</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:fmla='=TRIM(LEFT(A3,FIND(".",A3)+2))'> Total Property, Plant, & Equipment 6,714.7</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18>Product Trade Names 0.0</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:fmla='=TRIM(LEFT(A4,FIND(".",A4)+2))'> Product Trade Names 0.0</TD></TR></TBODY></TABLE>

Column B has the text part and the first number from Column A. Is there a way that can put what is not in column B, but in Column A in another column, say Column C. For the example above, column C should contain the data as below:

Column C:
-
-
3,791.4 46.6 2,876.7-


Thanks ahead!
Li

 

[jindon]

try
in C1
=If(Not(IsError(x(A1,B1))),x(A1,B1),"")

Function x(ByVal txt1 As String, ByVal txt2 As String)
Dim myNum As String, temp As String
myNum = Trim(Mid(txt2(InStrRev(txt2, " "))))
temp = Split(txt1, myNum)(1)
x = Trim(Left(temp, InStr(temp & " ", " ")))
End Function

 

[Peter_SSs]

Sorry, in the above cases, there are no texts following a string of numbers.

9VBH100000 appears to have text following a string of numbers (though the string is only one long).

 

[jindon]

lydiainflorida

Can you just post the resut that you want from the sample you posted ?

 

[mfexcel]

I have some related questions:

Say I have two column of data as shown below:

Column A Column B

<TABLE style="WIDTH: 568pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=758 border=0 x:str><COLGROUP><COL style="WIDTH: 284pt; mso-width-source: userset; mso-width-alt: 13860" span=2 width=379><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 284pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=379 height=18>Construction in Progress / Adjustments 449.4</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 284pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=379 x:fmla='=TRIM(LEFT(A1,FIND(".",A1)+2))'>Construction in Progress / Adjustments 449.4</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18>Property, Plant, & Equipment 6,265.2 </TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:fmla='=TRIM(LEFT(A2,FIND(".",A2)+2))'>Property, Plant, & Equipment 6,265.2</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18>Total Property, Plant, & Equipment 6,714.7 3,791.4 46.6 2,876.7</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:fmla='=TRIM(LEFT(A3,FIND(".",A3)+2))'>Total Property, Plant, & Equipment 6,714.7</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18>Product Trade Names 0.0</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:fmla='=TRIM(LEFT(A4,FIND(".",A4)+2))'>Product Trade Names 0.0</TD></TR></TBODY></TABLE>

Column B has the text part and the first number from Column A. Is there a way that can put what is not in column B, but in Column A in another column, say Column C. For the example above, column C should contain the data as below:

Column C:
-
-
3,791.4 46.6 2,876.7-


Thanks ahead!
Li

Try this:
=RIGHT(A1,LEN(A1)-LEN(B1))

 

[Peter_SSs]

l2zhang

Another option for you. Formula in C1 copied down.

Excel Workbook
	A	B	C
1	Construction in Progress / Adjustments 449.4	Construction in Progress / Adjustments 449.4
2	Property, Plant, & Equipment 6,265.2	Property, Plant, & Equipment 6,265.2
3	Total Property, Plant, & Equipment 6,714.7 3,791.4 46.6 2,876.7	Total Property, Plant, & Equipment 6,714.7	3,791.4 46.6 2,876.7
4	Product Trade Names 0.0	Product Trade Names 0.0
Substitute