Separate text from numbers in a string?

LoriD

Board Regular
Joined
Apr 1, 2002
Messages
148
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.
 
#NAME? will be returned when Excel doesn't find the function.

You must have placed the code in the wrong place.
 
Upvote 0

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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
 
Upvote 0
How would you decide that one number in () needs to be moved to column 2 and yet another number in () does not?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
ABCD
1OriginalTextNumberNumber
2Accounts Receivable 4,267.2Accounts Receivable4267.24267.2
3Construction in Progress / Adjustments 1,461.4Construction in Progress / Adjustments1461.41461.4
4Property, Plant, & Equipment 20,372.3Property, Plant, & Equipment20372.320372.3
5Total Property, Plant, & Equipment - 11,839.2 377.2 9,617.3Total Property, Plant, & Equipment -11839.211839.2
6Goodwill (5) 53,390.7 15,138.6 - 38,252.1Goodwill (5)53390.753390.7
7Sub-Leases In-Place 7.3 - - 7.3Sub-Leases In-Place7.37.3
8Net Due to Affiliates (2,659.3)Net Due to Affiliates-2659.3-2659.3
9Net Due to Affiliates (6) (2,659.3)Net Due to Affiliates (6)-2659.3-2659.3
10Net Due to Affiliates (6) -2,659.3 100.3Net Due to Affiliates (6)-2659.3-2659.3
Extract Text and Number
 
Upvote 0
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
ABCD
1OriginalTextNumberNumber
2Accounts Receivable 4,267.2Accounts Receivable4267.24267.2
3Construction in Progress / Adjustments 1,461.4Construction in Progress / Adjustments1461.41461.4
4Property, Plant, & Equipment 20,372.3Property, Plant, & Equipment20372.320372.3
5Total Property, Plant, & Equipment - 11,839.2 377.2 9,617.3Total Property, Plant, & Equipment -11839.211839.2
6Goodwill (5) 53,390.7 15,138.6 - 38,252.1Goodwill (5)53390.753390.7
7Sub-Leases In-Place 7.3 - - 7.3Sub-Leases In-Place7.37.3
8Net Due to Affiliates (2,659.3)Net Due to Affiliates-2659.3-2659.3
9Net Due to Affiliates (6) (2,659.3)Net Due to Affiliates (6)-2659.3-2659.3
10Net Due to Affiliates (6) -2,659.3 100.3Net 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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,636
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top