Separate text from numbers in a string?

L

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.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
On 2002-05-30 13:45, LoriD wrote:
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.
Click to expand...

Would you give some typical examples?
 
Upvote 0
Yes, cells C1:100 may hold any of the following:
UL7:38 (Unexecused Late + the arrival time)
L5:38 (Late + Arrival time)
P8.0 (Personal + 8 hrs.)
UA8.0 (Unexcused absence + 8 hrs.)

There are 16 different combo scenarios. I want to extract the text somehow to cells AA1:100 and evaluate these reason codes. Thanks again.
 
Upvote 0
On 2002-05-30 14:00, LoriD wrote:
Yes, cells C1:100 may hold any of the following:
UL7:38 (Unexecused Late + the arrival time)
L5:38 (Late + Arrival time)
P8.0 (Personal + 8 hrs.)
UA8.0 (Unexcused absence + 8 hrs.)

There are 16 different combo scenarios. I want to extract the text somehow to cells AA1:100 and evaluate these reason codes. Thanks again.
Click to expand...

In AA1 enter and copy down:

=SUBSTITUTE(C1,RIGHT(C1,SUMPRODUCT((LEN(C1)-LEN(SUBSTITUTE(C1,{":",".",0,1,2,3,4,5,6,7,8,9},""))))),"")
 
Upvote 0
Lori,

Just as an alternative:

=IF(CODE(MID(A1,2,1))>64,LEFT(A1,2),LEFT(A1,1))

will extract the text portion of the string (assuming the characters 'A', 'UL' etc are (1) in either the first or second positions in the string and (2) captialised.

Hope this helps,

Paddy
 
Upvote 0
That worked great! Thankyou. A little more help, PLEASE???
How can I get Excel to highlight a cell or something, some kind of indicator, for every 4 "UL"'s?
 
Upvote 0
LoriD,

Easiest way to do this would be to use conditional formating:

Say a1 - e1 contained the following:
UL,UL,UL,UL,UB:

Hghlight the range a1 to e1,
go to format...conditional formating,
change 'cell value is' to 'formula is'
enter the following:
=COUNTIF($A$1:$E$1,"UL")>3
select a format from the options...

This will highlight the range a1 to e1 if the value 'UL' turns up more than 3 times.

Post back if a variation on this doesn't work..

Paddy
 
Upvote 0
Aladin Akyurek said:
In AA1 enter and copy down:

=SUBSTITUTE(C1,RIGHT(C1,SUMPRODUCT((LEN(C1)-LEN(SUBSTITUTE(C1,{":",".",0,1,2,3,4,5,6,7,8,9},""))))),"")
Click to expand...
TO Aladin Akyurek<SCRIPT type=text/javascript> vbmenu_register("postmenu_45559", true); </SCRIPT>

You have done great job in seperating text and numbers. that works well i need to separate the same vise versa eg. 2350pcs. , 255gross, 500doz etc pleace help me thanks Upul
 
Upvote 0
upulhettiarachchi said:
to aladin akyurek<script type=text/javascript> vbmenu_register("postmenu_45559", true); </script>

you have done great job in seperating text and numbers. That works well i need to separate the same vise versa eg. 2350pcs. , 255gross, 500doz etc pleace help me thanks upul
Click to expand...

Try...

=LOOKUP(9.99999999999999E+307,--(LEFT(A2,ROW(INDIRECT("1:"&LEN(A2))))))
 
Upvote 0
Hi!<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
I have a related problem with a text and number string as shown below:<o:p></o:p>
<TABLE class=MsoNormalTable style="WIDTH: 333pt; BORDER-COLLAPSE: collapse; mso-padding-alt: 0in 0in 0in 0in" cellSpacing=0 cellPadding=0 width=444 border=0 u1:str><COLGROUP><COL style="WIDTH: 299pt; mso-width-source: userset; mso-width-alt: 14555" width=398><TBODY><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 0in; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #d4d0c8; WIDTH: 333pt; PADDING-TOP: 0in; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=444>Accounts Receivable 4,267.2<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 1"><TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 0in; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #d4d0c8; WIDTH: 333pt; PADDING-TOP: 0in; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=444>Construction in Progress / Adjustments 1,461.4<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 2"><TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 0in; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #d4d0c8; WIDTH: 333pt; PADDING-TOP: 0in; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=444>Property, Plant, & Equipment 20,372.3<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 3"><TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 0in; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #d4d0c8; WIDTH: 333pt; PADDING-TOP: 0in; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=444>Total Property, Plant, & Equipment - 11,839.2 377.2 9,617.3<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 4; mso-yfti-lastrow: yes"><TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 0in; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #d4d0c8; WIDTH: 333pt; PADDING-TOP: 0in; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=444>Goodwill (5) 53,390.7 15,138.6 - 38,252.1<o:p></o:p>
</TD></TR></TBODY></TABLE>
Sub-Leases In-Place 7.3 - - 7.3
<o:p> </o:p>
I would just like to have the whole text string in the first column and the first data in the data sequence to be in the second column; in some case there are /, (5) and “,” and “&” wild characters in the string make it harder to separate, and other cases I have a couple of data sequence, and “-“ is missing data and I would like to have it as well, but this confuse with the “-“ in the text strings.
<o:p> </o:p>
So the clean-up data should look like:
<TABLE class=MsoNormalTable style="WIDTH: 333pt; BORDER-COLLAPSE: collapse; mso-padding-alt: 0in 0in 0in 0in" cellSpacing=0 cellPadding=0 width=444 border=0><TBODY><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 0in; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #d4d0c8; WIDTH: 333pt; PADDING-TOP: 0in; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=444>Accounts Receivable 4,267.2<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 1"><TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 0in; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #d4d0c8; WIDTH: 333pt; PADDING-TOP: 0in; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=444>Construction in Progress / Adjustments 1,461.4<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 2"><TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 0in; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #d4d0c8; WIDTH: 333pt; PADDING-TOP: 0in; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=444>Property, Plant, & Equipment 20,372.3<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 3"><TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 0in; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #d4d0c8; WIDTH: 333pt; PADDING-TOP: 0in; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=444>Total Property, Plant, & Equipment - <o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 4; mso-yfti-lastrow: yes"><TD style="BORDER-RIGHT: #d4d0c8; PADDING-RIGHT: 0in; BORDER-TOP: #d4d0c8; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: #d4d0c8; WIDTH: 333pt; PADDING-TOP: 0in; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=444>Goodwill (5) 53,390.7 <o:p></o:p>
</TD></TR></TBODY></TABLE>
Sub-Leases In-Place 7.3
<o:p> </o:p>
<o:p> </o:p>
Anyone knows how to do it, Please give a pointer and I would really appreciate it.
<o:p> </o:p>
Thanks ahead!
Li
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
How to remove variably placed text within a string of text by looking at the columns to the right
Replies
1
Views
77
Alex Blakenburg
A
A
  • Question Question
Parsing text with different number of dashes
Replies
6
Views
265
alansidman
A
H
  • Question Question
List out duplicate items in a list with different costs/values
Replies
6
Views
132
H3MSY
H
F
  • Solved
Colour all blank cells in a row if one cell in row has text.
Replies
3
Views
382
Joe4
Joe4
T
  • Question Question
Split content on new sheet and extract info in parentheses
2
Replies
10
Views
240
DanteAmor
DanteAmor

Forum statistics

Threads
1,223,243
Messages
6,170,967
Members
452,371
Latest member
Frana

Share this page

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