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.

 

[jindon]

#NAME? will be returned when Excel doesn't find the function.

You must have placed the code in the wrong place.

 

[l2zhang]

Hi Peter,
Thank you so much for the formula. Like what you said, it does not work if the first number is missing with "-". I guess I can always replace the "-" with "0.0" and then replace it back when it is done.

I am a novice for excel. I am wondering if there is a way I can put this process into a VBA code since I have more than 50 files with exact the same accounting inputs (many obserables) that I need to separate the accounting variables from the numbers. Any hints will be really helpful!

Thanks ahead!
Li

As I am working through the data, I realize that some of the data with the line like:
<TABLE style="WIDTH: 299pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=398 border=0 x:str><COLGROUP><COL style="WIDTH: 299pt; mso-width-source: userset; mso-width-alt: 14555" width=398><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 299pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=398 height=17>Net Due to Affiliates (2,659.3)

</TD></TR></TBODY></TABLE>
In accounting sense, the data in () is a negative number. Is there any way that the formula can be adjusted to separate this line to be:
Column 1 Column 2
Net Due to Affilates (2,659.3)

But this should be different than the previous Line:
Goodwill (5) 53,390.7 15,138.6 - 38,252.1, which we want it to be:

Column 1 Column 2
Goodwill (5) 53,390.7

I will really appreciate some help on this one.

Best,
Li

 

[Peter_SSs]

How would you decide that one number in () needs to be moved to column 2 and yet another number in () does not?

 

[AdamL]

Hi l2zhang

Just out of curiosity, did you try these formulae in B1 and C1 respectively?

=LEFT(A1,LOOKUP(2,1/NOT(ISNUMBER(FIND(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"0123456789-. "))),ROW(INDIRECT("1:"&LEN(A1)))))

=LOOKUP(9.99999999999999E+307,--LEFT(SUBSTITUTE(SUBSTITUTE(A1,B1,""),"- ","0 "),ROW(INDIRECT("1:"&LEN(A1)-LEN(B1)))))

Also, I agree with Peter that there appears to be no way to differentiate one () from another - unless, for example, the "numeric" () always has a decimal and the "text" () always has an integer.

 

[l2zhang]

How would you decide that one number in () needs to be moved to column 2 and yet another number in () does not?

Hi Peter,
Thank you for such a fast reply. the numbers in () without the first decimal point goes with the text, such as Goodwill (5), and there are not really accounting data; while the numbers in () with the first decimal point are the real accounting data, which refers to the negative numbers. Is it possible to separate them then?

Thx,
Li

 

[Peter_SSs]

Hi Peter,
Thank you for such a fast reply. the numbers in () without the first decimal point goes with the text, such as Goodwill (5), and there are not really accounting data; while the numbers in () with the first decimal point are the real accounting data, which refers to the negative numbers. Is it possible to separate them then?

Thx,
Li

Try this. I have given two possible formulas to extract the required number. The one in column C is a bit shorter but if it does not work with all your data, you could try the one in column D, though I think it will give the same results.

Excel Workbook
	A	B	C	D
1	Original	Text	Number	Number
2	Accounts Receivable 4,267.2	Accounts Receivable	4267.2	4267.2
3	Construction in Progress / Adjustments 1,461.4	Construction in Progress / Adjustments	1461.4	1461.4
4	Property, Plant, & Equipment 20,372.3	Property, Plant, & Equipment	20372.3	20372.3
5	Total Property, Plant, & Equipment - 11,839.2 377.2 9,617.3	Total Property, Plant, & Equipment -	11839.2	11839.2
6	Goodwill (5) 53,390.7 15,138.6 - 38,252.1	Goodwill (5)	53390.7	53390.7
7	Sub-Leases In-Place 7.3 - - 7.3	Sub-Leases In-Place	7.3	7.3
8	Net Due to Affiliates (2,659.3)	Net Due to Affiliates	-2659.3	-2659.3
9	Net Due to Affiliates (6) (2,659.3)	Net Due to Affiliates (6)	-2659.3	-2659.3
10	Net Due to Affiliates (6) -2,659.3 100.3	Net Due to Affiliates (6)	-2659.3	-2659.3
Extract Text and Number

 

[l2zhang]

Try this. I have given two possible formulas to extract the required number. The one in column C is a bit shorter but if it does not work with all your data, you could try the one in column D, though I think it will give the same results.

Excel Workbook
	A	B	C	D
1	Original	Text	Number	Number
2	Accounts Receivable 4,267.2	Accounts Receivable	4267.2	4267.2
3	Construction in Progress / Adjustments 1,461.4	Construction in Progress / Adjustments	1461.4	1461.4
4	Property, Plant, & Equipment 20,372.3	Property, Plant, & Equipment	20372.3	20372.3
5	Total Property, Plant, & Equipment - 11,839.2 377.2 9,617.3	Total Property, Plant, & Equipment -	11839.2	11839.2
6	Goodwill (5) 53,390.7 15,138.6 - 38,252.1	Goodwill (5)	53390.7	53390.7
7	Sub-Leases In-Place 7.3 - - 7.3	Sub-Leases In-Place	7.3	7.3
8	Net Due to Affiliates (2,659.3)	Net Due to Affiliates	-2659.3	-2659.3
9	Net Due to Affiliates (6) (2,659.3)	Net Due to Affiliates (6)	-2659.3	-2659.3
10	Net Due to Affiliates (6) -2,659.3 100.3	Net Due to Affiliates (6)	-2659.3	-2659.3
Extract Text and Number

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

 

[Peter_SSs]

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?

I have not used books, and have no formal training in vba. What vba I have learned has been largely from trial & error, the built-in vba Help, and this forum.

Good luck with it! I think the main thing is to get in and have a go.

 

[l2zhang]

How to return the column letter when you find the searched text?

Say,
If I am looking for a text " Texas RSA 9 B1 LP" in a range of cells and then I find it, how can I return the column letter say if it is "C", so I can refer to it later in another formula. Is it such a function can preform the task, or I have to write a macro for it?

And how can I refer back to this returned column letter in another formula which has "C" and row numbers attached to it (for example, C1, C2, ...)?

Your help will be more than appreciate it!

Li

 

[Peter_SSs]

Re: How to return the column letter when you find the searched text?

Li

You would be best to start a new thread for this question as it is not related to the title of this thread.

When you do start a new thread some clarification would help:

1. The range you are looking in: Is it a single row (eg D2:G2), or multiple rows (eg D2:G10)?

2. Is it possible that the text you are searching for occurs more than once in the range? If so, how do you expect to handle that situation?

3. If you could provide some sample data and describe in more specific terms what you want to do in relation to that sample data, it would help.