|
Extract a number(s) from a text string
I have an single column of data (see below) that randomly has numbers inserted at the beginning of the text. The database unfortunately cannot be altered at this time, so I'm stuck. I need this data in two columns. Obviusly "text to columns" won't work, or I wouldn't be asking for help. Anyone have any suggestions? 57NORCROSS EAST 213DUBLIN 57NORCROSS WEST 48DOWNTOWN NORTH 26MCDONOUGH 48DOWNTOWN SOUTH 53NEWNAN 134BUFORD 135MACON 134SUWANEE 121FULTON INDUSTRIAL SOUTH 59LAWRENCEVILLE 1009AUSTELL 134DULUTH 130LITHIA SPRINGS 106ROSWELL 7WEST GEORGIA 123DAWSONVILLE 100CEDARTOWN 125MARIETTA SOUTH 154ROME 5HOOTCH[/i][/i] -- Christopher770 ------------------------------------------------------------------------ Christopher770's Profile: http://www.thecodecage.com/forumz/member.php?userid=188 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=77789 |
Extract a number(s) from a text string
Christopher.
To extract the numbers use =LEFT(A1,LEN(LOOKUP(10^23,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789")),ROW(INDIRECT("1:"&LEN(A1))))))) and the text =RIGHT(A1,LEN(A1)-LEN(LOOKUP(10^23,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789")),ROW(INDIRECT("1:"&LEN(A1))))))) Mike "Christopher770" wrote: I have an single column of data (see below) that randomly has numbers inserted at the beginning of the text. The database unfortunately cannot be altered at this time, so I'm stuck. I need this data in two columns. Obviusly "text to columns" won't work, or I wouldn't be asking for help. Anyone have any suggestions? 57NORCROSS EAST 213DUBLIN 57NORCROSS WEST 48DOWNTOWN NORTH 26MCDONOUGH 48DOWNTOWN SOUTH 53NEWNAN 134BUFORD 135MACON 134SUWANEE 121FULTON INDUSTRIAL SOUTH 59LAWRENCEVILLE 1009AUSTELL 134DULUTH 130LITHIA SPRINGS 106ROSWELL 7WEST GEORGIA 123DAWSONVILLE 100CEDARTOWN 125MARIETTA SOUTH 154ROME 5HOOTCH[/i] -- Christopher770 ------------------------------------------------------------------------ Christopher770's Profile: http://www.thecodecage.com/forumz/member.php?userid=188 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=77789 [/i] |
Extract a number(s) from a text string
With
A1 containing a text string beginning with numbers this formula returns only the beginning numbers: B1: =LOOKUP(99^99,--("0"&MID(A1,MIN( SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW ($1:$10000)))) To return the text without those numbers C1: =MID(A1,LEN(B1)+1,255) If A1 contains: 57NORCROSS EAST B1 returns: 57 C1 returns: NORCROSS EAST Does that help? Regards, Ron Coderre Microsoft MVP (Excel) "Christopher770" wrote in message ... I have an single column of data (see below) that randomly has numbers inserted at the beginning of the text. The database unfortunately cannot be altered at this time, so I'm stuck. I need this data in two columns. Obviusly "text to columns" won't work, or I wouldn't be asking for help. Anyone have any suggestions? 57NORCROSS EAST 213DUBLIN 57NORCROSS WEST 48DOWNTOWN NORTH 26MCDONOUGH 48DOWNTOWN SOUTH 53NEWNAN 134BUFORD 135MACON 134SUWANEE 121FULTON INDUSTRIAL SOUTH 59LAWRENCEVILLE 1009AUSTELL 134DULUTH 130LITHIA SPRINGS 106ROSWELL 7WEST GEORGIA 123DAWSONVILLE 100CEDARTOWN 125MARIETTA SOUTH 154ROME 5HOOTCH[/i] -- Christopher770 ------------------------------------------------------------------------ Christopher770's Profile: http://www.thecodecage.com/forumz/member.php?userid=188 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=77789 [/i] |
Extract a number(s) from a text string
This was proposed by Ashish Mathur
http://office.microsoft.com/en-us/ex...549011033.aspx This will extract the first set of numbers in any string... If you have strings with numbers in Col A then paste this in B1 =1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9), 1)),0),COUNT(1*MID(A1,ROW($1:$9),1))) and press CTRL-SHIFT-ENTER (this will put {} around the formula as this is an ARRAY formula and copy the formula down "Christopher770" wrote: I have an single column of data (see below) that randomly has numbers inserted at the beginning of the text. The database unfortunately cannot be altered at this time, so I'm stuck. I need this data in two columns. Obviusly "text to columns" won't work, or I wouldn't be asking for help. Anyone have any suggestions? 57NORCROSS EAST 213DUBLIN 57NORCROSS WEST 48DOWNTOWN NORTH 26MCDONOUGH 48DOWNTOWN SOUTH 53NEWNAN 134BUFORD 135MACON 134SUWANEE 121FULTON INDUSTRIAL SOUTH 59LAWRENCEVILLE 1009AUSTELL 134DULUTH 130LITHIA SPRINGS 106ROSWELL 7WEST GEORGIA 123DAWSONVILLE 100CEDARTOWN 125MARIETTA SOUTH 154ROME 5HOOTCH[/i] -- Christopher770 ------------------------------------------------------------------------ Christopher770's Profile: http://www.thecodecage.com/forumz/member.php?userid=188 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=77789 [/i] |
Extract a number(s) from a text string
On Mar 21, 7:43 am, Christopher770
wrote: I have an single column of data (see below) that randomly has numbers inserted at the beginning of the text. [....] I need this data in two columns. I would write a macro. Are you interested in a VBA solution? Alternatively, I would put the following formula into a parallel column (say column D): =MIN(FIND ({"A","B","C","D","E","F","G","H","I","J","K","L", "M","N","O","P","Q","R","S","T","U","V","W","X","Y ","Z"},A1&"ABCDEFGHIJKLMNOPQRSTUVWXYZ")-1)) In another parallel column (say column B), put: =--LEFT(A1,D1-1) In yet another parallel column (say column C), put: =RIGHT(A1,LEN(A1)-D1+1) If you want to "replace" the column of original data (column A in my example), copy-and-paste-special-value the columns with the LEFT and RIGHT formulas. Then delete the columns with the FIND formula and the original data. HTH. ----- original posting ----- On Mar 21, 7:43*am, Christopher770 wrote: I have an single column of data (see below) that randomly has numbers inserted at the beginning of the text. The database unfortunately cannot be altered at this time, so I'm stuck. I need this data in two columns. Obviusly "text to columns" won't work, or I wouldn't be asking for help. Anyone have any suggestions? 57NORCROSS EAST 213DUBLIN 57NORCROSS WEST 48DOWNTOWN NORTH 26MCDONOUGH 48DOWNTOWN SOUTH 53NEWNAN 134BUFORD 135MACON 134SUWANEE 121FULTON INDUSTRIAL SOUTH 59LAWRENCEVILLE 1009AUSTELL 134DULUTH 130LITHIA SPRINGS 106ROSWELL 7WEST GEORGIA 123DAWSONVILLE 100CEDARTOWN 125MARIETTA SOUTH 154ROME 5HOOTCH[/i] -- Christopher770 ------------------------------------------------------------------------ Christopher770's Profile:http://www.thecodecage.com/forumz/member.php?userid=188 View this thread:http://www.thecodecage.com/forumz/sh...ad.php?t=77789[/i] |
Extract a number(s) from a text string
Slight improvement....
In one parallel column: =--LEFT(A1,MIN(FIND ({"A","B","C","D","E","F","G","H","I","J","K","L", "M","N","O","P","Q","R","S","T","U","V","W","X","Y ","Z"},A1&"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))-1) In another parallel column: =RIGHT(A1,LEN(A1)-LEN(B1)) Eliminates the need for a helper column with FIND formulas. ----- original posting ----- On Mar 21, 8:44*am, joeu2004 wrote: On Mar 21, 7:43 am, Christopher770 wrote: I have an single column of data (see below) that randomly has numbers inserted at the beginning of the text. [....] I need this data in two columns. I would write a macro. *Are you interested in a VBA solution? Alternatively, I would put the following formula into a parallel column (say column D): =MIN(FIND ({"A","B","C","D","E","F","G","H","I","J","K","L", "M","N","O","P","Q","R","*S","T","U","V","W","X"," Y","Z"},A1&"ABCDEFGHIJKLMNOPQRSTUVWXYZ")-1)) In another parallel column (say column B), put: =--LEFT(A1,D1-1) In yet another parallel column (say column C), put: =RIGHT(A1,LEN(A1)-D1+1) If you want to "replace" the column of original data (column A in my example), copy-and-paste-special-value the columns with the LEFT and RIGHT formulas. *Then delete the columns with the FIND formula and the original data. HTH. ----- original posting ----- On Mar 21, 7:43*am, Christopher770 wrote: I have an single column of data (see below) that randomly has numbers inserted at the beginning of the text. The database unfortunately cannot be altered at this time, so I'm stuck. I need this data in two columns. Obviusly "text to columns" won't work, or I wouldn't be asking for help.. Anyone have any suggestions? 57NORCROSS EAST 213DUBLIN 57NORCROSS WEST 48DOWNTOWN NORTH 26MCDONOUGH 48DOWNTOWN SOUTH 53NEWNAN 134BUFORD 135MACON 134SUWANEE 121FULTON INDUSTRIAL SOUTH 59LAWRENCEVILLE 1009AUSTELL 134DULUTH 130LITHIA SPRINGS 106ROSWELL 7WEST GEORGIA 123DAWSONVILLE 100CEDARTOWN 125MARIETTA SOUTH 154ROME 5HOOTCH[/i] -- Christopher770 ------------------------------------------------------------------------ Christopher770's Profile:http://www.thecodecage.com/forumz/member.php?userid=188 View this thread:http://www.thecodecage.com/forumz/sh...ad.php?t=77789[/i] |
Extract a number(s) from a text string
Hi Christopher and welcome to the board I found this link 'converting strings to numbers - microsoft.public.excel.worksheet.functions | Google Groups' (http://tinyurl.com/cxknjs) which is a UDF to extract the first string of digits in a string. HTH -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)* & allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=77789 |
Extract a number(s) from a text string
Here's another one...
Array entered** : =--LEFT(A1,MATCH(TRUE,ISERR(-MID(A1,ROW(INDIRECT("1:15")),1)),0)-1) Note that this will return the number as a number. It assumes no number string will be longer than 15 digits. Also note that Excel doesn't like leading zeros with real numbers so this will strip off any leading zeros. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. For the text portion of the string... If there are leading zeros this won't work properly. Assuming the above formula is entered in D1. =MID(A1,LEN(D1)+1,255) -- Biff Microsoft Excel MVP "Christopher770" wrote in message ... I have an single column of data (see below) that randomly has numbers inserted at the beginning of the text. The database unfortunately cannot be altered at this time, so I'm stuck. I need this data in two columns. Obviusly "text to columns" won't work, or I wouldn't be asking for help. Anyone have any suggestions? 57NORCROSS EAST 213DUBLIN 57NORCROSS WEST 48DOWNTOWN NORTH 26MCDONOUGH 48DOWNTOWN SOUTH 53NEWNAN 134BUFORD 135MACON 134SUWANEE 121FULTON INDUSTRIAL SOUTH 59LAWRENCEVILLE 1009AUSTELL 134DULUTH 130LITHIA SPRINGS 106ROSWELL 7WEST GEORGIA 123DAWSONVILLE 100CEDARTOWN 125MARIETTA SOUTH 154ROME 5HOOTCH[/i] -- Christopher770 ------------------------------------------------------------------------ Christopher770's Profile: http://www.thecodecage.com/forumz/member.php?userid=188 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=77789 [/i] |
Extract a number(s) from a text string
One more method to add to the pile<g...
=LOOKUP(999999,--LEFT(A13,ROW($1:$99))) The use of the 999999 assumes you will never have a leading number of one million or more (if you could, then just make the 999999 a number larger than your largest possible leading number you could ever have). -- Rick (MVP - Excel) "Christopher770" wrote in message ... I have an single column of data (see below) that randomly has numbers inserted at the beginning of the text. The database unfortunately cannot be altered at this time, so I'm stuck. I need this data in two columns. Obviusly "text to columns" won't work, or I wouldn't be asking for help. Anyone have any suggestions? 57NORCROSS EAST 213DUBLIN 57NORCROSS WEST 48DOWNTOWN NORTH 26MCDONOUGH 48DOWNTOWN SOUTH 53NEWNAN 134BUFORD 135MACON 134SUWANEE 121FULTON INDUSTRIAL SOUTH 59LAWRENCEVILLE 1009AUSTELL 134DULUTH 130LITHIA SPRINGS 106ROSWELL 7WEST GEORGIA 123DAWSONVILLE 100CEDARTOWN 125MARIETTA SOUTH 154ROME 5HOOTCH[/i] -- Christopher770 ------------------------------------------------------------------------ Christopher770's Profile: http://www.thecodecage.com/forumz/member.php?userid=188 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=77789 [/i] |
Extract a number(s) from a text string
On Mar 22, 1:16 pm, "Rick Rothstein"
=LOOKUP(999999,--LEFT(A13,ROW($1:$99))) A superior solution, IMHO. It might also be noted that the formula assumes that the original text (A13) is no more than 99 characters. Alternatively: =LOOKUP(99999,--LEFT(A13,ROW(OFFSET($A$1,0,0,LEN(A13))))) In either case, the right-hand portion of the original text can be extracted with: =RIGHT(A13,LEN(A13)-LEN(B13)) if the LOOKUP formula is in B13. ----- original posting ----- On Mar 22, 1:16*pm, "Rick Rothstein" wrote: One more method to add to the pile<g... =LOOKUP(999999,--LEFT(A13,ROW($1:$99))) The use of the 999999 assumes you will never have a leading number of one million or more (if you could, then just make the 999999 a number larger than your largest possible leading number you could ever have). -- Rick (MVP - Excel) "Christopher770" wrote in message ... I have an single column of data (see below) that randomly has numbers inserted at the beginning of the text. The database unfortunately cannot be altered at this time, so I'm stuck. I need this data in two columns. Obviusly "text to columns" won't work, or I wouldn't be asking for help.. Anyone have any suggestions? 57NORCROSS EAST 213DUBLIN 57NORCROSS WEST 48DOWNTOWN NORTH 26MCDONOUGH 48DOWNTOWN SOUTH 53NEWNAN 134BUFORD 135MACON 134SUWANEE 121FULTON INDUSTRIAL SOUTH 59LAWRENCEVILLE 1009AUSTELL 134DULUTH 130LITHIA SPRINGS 106ROSWELL 7WEST GEORGIA 123DAWSONVILLE 100CEDARTOWN 125MARIETTA SOUTH 154ROME 5HOOTCH[/i] -- Christopher770 ------------------------------------------------------------------------ Christopher770's Profile: http://www.thecodecage.com/forumz/member.php?userid=188 View this thread:http://www.thecodecage.com/forumz/sh...ad.php?t=77789[/i] |
Extract a number(s) from a text string
See inline comments...
=LOOKUP(999999,--LEFT(A13,ROW($1:$99))) A superior solution, IMHO. Thanks! It might also be noted that the formula assumes that the original text (A13) is no more than 99 characters. The text String can be longer and it won't matter as the number of digits at the beginning is assumed to be no more than 6 digits long. As a matter of fact, given that assumption, the formula could have been this instead... =LOOKUP(999999,--LEFT(A13,ROW($1:$6))) and it would work fine. Alternatively: =LOOKUP(99999,--LEFT(A13,ROW(OFFSET($A$1,0,0,LEN(A13))))) I try to stay away from using OFFSET as it is a Volatile function. As I said above, accounting for this is unnecessary for the reason I stated there; but also, maybe more importantly, since we are returning an actual number (rather than text), Excel won't support a number anywhere near as long as 99 digits, so accounting for a string longer than that is unnecessary. My use of 99 was more out of habit (I like the repeating digits) than necessity. In either case, the right-hand portion of the original text can be extracted with: =RIGHT(A13,LEN(A13)-LEN(B13)) if the LOOKUP formula is in B13. I realize this is more a personal preference issue, but I would use one less function call and do it this way... =MID(A13,LEN(A13)+1,99) again, recognizing that if the text in A13 **could** be longer than 99 characters, then the 99 would need to be increase (my choice would be to make it 999 in that case). -- Rick (MVP - Excel) |
Extract a number(s) from a text string
On Mar 22, 3:21 pm, "Rick Rothstein"
wrote: The text String can be longer and it won't matter as the number of digits at the beginning is assumed to be no more than 6 digits long. [.... and] Excel won't support a number anywhere near as long as 99 digits Good points! I wasn't thinking. ----- original posting ----- On Mar 22, 3:21*pm, "Rick Rothstein" wrote: See inline comments... =LOOKUP(999999,--LEFT(A13,ROW($1:$99))) A superior solution, IMHO. Thanks! It might also be noted that the formula assumes that the original text (A13) is no more than 99 characters. The text String can be longer and it won't matter as the number of digits at the beginning is assumed to be no more than 6 digits long. As a matter of fact, given that assumption, the formula could have been this instead... =LOOKUP(999999,--LEFT(A13,ROW($1:$6))) and it would work fine. Alternatively: =LOOKUP(99999,--LEFT(A13,ROW(OFFSET($A$1,0,0,LEN(A13))))) I try to stay away from using OFFSET as it is a Volatile function. As I said above, accounting for this is unnecessary for the reason I stated there; but also, maybe more importantly, since we are returning an actual number (rather than text), Excel won't support a number anywhere near as long as 99 digits, so accounting for a string longer than that is unnecessary. My use of 99 was more out of habit (I like the repeating digits) than necessity. In either case, the right-hand portion of the original text can be extracted with: =RIGHT(A13,LEN(A13)-LEN(B13)) if the LOOKUP formula is in B13. I realize this is more a personal preference issue, but I would use one less function call and do it this way... =MID(A13,LEN(A13)+1,99) again, recognizing that if the text in A13 **could** be longer than 99 characters, then the 99 would need to be increase (my choice would be to make it 999 in that case). -- Rick (MVP - Excel) |
| All times are GMT +1. The time now is 12:03 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com