About separate numbers from a text by Excel Function

M

minhthien321

New Member
Joined
Sep 6, 2009
Messages
22
Dear All,

By the formula of Excel, What function can separate numbers from a text with structure NumberText, the length of the Number or Text unlimited.
Eg: 14568minhthien, the result is 14568.

Thanks for helping me.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
try this formula
=LEFT(A1,5)

if the numbers are moving then formula has to be changed accordingly
 
Upvote 0
Thank you, but it only for 1 case, if I have a chain of text: 123nghhia, 1234nghiadk, 12345lskdjflkd, 123456ngalskdjfk ... it cannot do exactly!

I mean Len(Number) or Len(Text) in the text is unlimited.
 
Upvote 0
Here's a formula I bodged together a while back

=--LEFT(A1,MAX(NOT(ISERROR(--(LEFT($A1,ROW(OFFSET($A$1,0,0,LEN($A1),1))))))*ROW(OFFSET($A$1,0,0,LEN($A1),1))))

You should paste it to the cell and confirm with ctrl-shift-enter (an array formula)

If A1 is not your target cell, only change the references in bold.

I'm sure there's an easier way...
 
Upvote 0
Try this, hope it help. this was post long ago here by <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
Aladin Akyurek <o:p></o:p>
<o:p></o:p>
<TABLE style="WIDTH: 100%; mso-padding-alt: 4.5pt 4.5pt 4.5pt 4.5pt; mso-cellspacing: 0in; mso-yfti-tbllook: 1184" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width="100%"><TBODY><TR style="mso-yfti-irow: 0; mso-yfti-firstrow: yes; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: white 1pt solid; BORDER-LEFT: white 1pt solid; PADDING-BOTTOM: 4.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 4.5pt; WIDTH: 131.25pt; PADDING-RIGHT: 4.5pt; BORDER-TOP: white 1pt solid; BORDER-RIGHT: white 1pt solid; PADDING-TOP: 4.5pt; mso-border-top-alt: .25pt; mso-border-left-alt: 1.0pt; mso-border-bottom-alt: .25pt; mso-border-right-alt: 1.0pt; mso-border-color-alt: white; mso-border-style-alt: solid" vAlign=top width=175> <o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 4.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 4.5pt; PADDING-RIGHT: 4.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: white 1pt solid; PADDING-TOP: 4.5pt" vAlign=top> <o:p></o:p>
Re: Separate text from numbers in a string? <o:p></o:p>
<HR style="COLOR: white" align=center SIZE=1 width="100%" noShade>​
Quote: <o:p></o:p>
<TABLE style="WIDTH: 100%; mso-padding-alt: 4.5pt 4.5pt 4.5pt 4.5pt; mso-cellspacing: 0in; mso-yfti-tbllook: 1184" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width="100%"><TBODY><TR style="mso-yfti-irow: 0; mso-yfti-firstrow: yes; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #ece9d8 1pt inset; BORDER-LEFT: #ece9d8 1pt inset; PADDING-BOTTOM: 4.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 4.5pt; PADDING-RIGHT: 4.5pt; BORDER-TOP: #ece9d8 1pt inset; BORDER-RIGHT: #ece9d8 1pt inset; PADDING-TOP: 4.5pt">Originally Posted by coolingaid <?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" o:preferrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><v:path o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></v:path><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><v:shape style="WIDTH: 9pt; HEIGHT: 9pt; VISIBILITY: visible; mso-wrap-style: square" id=Picture_x0020_2 target="_blank" href="http://www.mrexcel.com/forum/showthread.php?p=2300954#post2300954" o:button="t" alt="http://www.mrexcel.com/forum/images/buttons/viewpost.gif" type="#_x0000_t75" o:spid="_x0000_i1027"><v:imagedata o:title="viewpost" src="file:///C:\DOCUME~1\ChinhNTK\LOCALS~1\Temp\msohtmlclip1\01\clip_image001.gif"></v:imagedata></v:shape><o:p></o:p>
hello ... newbie here <v:shape style="WIDTH: 11.25pt; HEIGHT: 11.25pt; VISIBILITY: visible; mso-wrap-style: square" id=Picture_x0020_3 alt="http://www.mrexcel.com/forum/images/smilies/icon_smile.gif" type="#_x0000_t75" o:spid="_x0000_i1026"><v:imagedata o:title="icon_smile" src="file:///C:\DOCUME~1\ChinhNTK\LOCALS~1\Temp\msohtmlclip1\01\clip_image002.gif"></v:imagedata></v:shape><o:p></o:p>
<o:p></o:p>
need help not only to separate the text from number, but also need the number data as well...<o:p></o:p>
<o:p></o:p>
for example:<o:p></o:p>
A1<o:p></o:p>
AX10<o:p></o:p>
FR100<o:p></o:p>
<o:p></o:p>
then i need the data to be separated to text and number:<o:p></o:p>
A 1<o:p></o:p>
AX 100<o:p></o:p>
FR 1000<o:p></o:p>
<o:p></o:p>
thanks in advance<o:p></o:p>
</TD></TR></TBODY></TABLE><o:p> </o:p>
<TABLE style="WIDTH: 2in; BORDER-COLLAPSE: collapse; mso-padding-alt: 0in 0in 0in 0in; mso-yfti-tbllook: 1184" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=192><TBODY><TR style="HEIGHT: 0.2in; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 48pt; PADDING-RIGHT: 0in; HEIGHT: 0.2in; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-alt: solid windowtext .5pt" width=64></TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 48pt; PADDING-RIGHT: 0in; HEIGHT: 0.2in; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-top-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" width=64></TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 48pt; PADDING-RIGHT: 0in; HEIGHT: 0.2in; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-top-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" width=64></TD></TR><TR style="HEIGHT: 0.2in; mso-yfti-irow: 1"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 0.2in; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-left-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">A1 <o:p></o:p>
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 0.2in; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">A<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 0.2in; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">
1<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 0.2in; mso-yfti-irow: 2"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 0.2in; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-left-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">AX10<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 0.2in; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">AX<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 0.2in; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">
10<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 0.2in; mso-yfti-irow: 3"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 0.2in; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-left-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">FR100<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 0.2in; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">FR<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 0.2in; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">
100<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 0.2in; mso-yfti-irow: 4; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 0.2in; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-left-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt"></TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 0.2in; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt"></TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 0.2in; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt"></TD></TR></TBODY></TABLE> <o:p></o:p>
B2, just enter and copy down:<o:p></o:p>
<o:p></o:p>
=TRIM(LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")-1)))<o:p></o:p>
<o:p></o:p>
C2, just enter and copy down:<o:p></o:p>
<o:p></o:p>
=SUBSTITUTE(A2,B2,"")+0<o:p></o:p>
<o:p></o:p>
You might want to omit the +0 bit if text numbers are OK. <o:p></o:p>
</TD></TR></TBODY></TABLE>​
 
Upvote 0
Thanks All, it's so wonderful!

I'd like to ask 1 more question:

If a tructure of text is TextNumber, it means "minhthien321" => 321, how can I do? (If I don't want to use MoreFunc?)

Thanks.
 
Upvote 0
minhthien321 said:
Thanks All, it's so wonderful!

I'd like to ask 1 more question:

If a tructure of text is TextNumber, it means "minhthien321" => 321, how can I do? (If I don't want to use MoreFunc?)

Thanks.
Click to expand...

Try for numbers at the end...

A2: minhthien321

=REPLACE(A2,1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")-1),"")+0<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
 
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
135
Alex Blakenburg
A
G
  • Solved
Formula to efficiently find unique set from a number of numeric arrays
Replies
3
Views
108
Peter_SSs
Peter_SSs
sharky12345
  • Question Question
Get numbers only from each line in textbox
Replies
4
Views
162
sharky12345
sharky12345
M
  • Question Question
Dynamic link from Word to Excel...
Replies
8
Views
252
Matteo85
M
A
  • Question Question
Parsing text with different number of dashes
Replies
6
Views
374
alansidman
A

Forum statistics

Threads
1,224,597
Messages
6,179,813
Members
452,945
Latest member
Bib195

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