Home |
Search |
Today's Posts |
#1
|
|||
|
|||
extracting numbers from variable text
hi I am trying to extract a numeric value from a column of text that does not always have any relevant data. Typical column entries are like this: Employees: 46 | Employee Growth: -4.17% Employees: 3336 | Employee Growth: -5.66% Employees: 1700 | Employee Growth: % but in this mixed bag of data there are many odd entries of free text e.g. Two areas are using xx. Production studio's . . . . . The data I want to extract is the number of employees, which can be anything between a 1 and 6 digit number. I can do a basic extraction using =MID(cellref, 12,6) to get the raw information, but this will not always return a number. I need to remove the junk and just be left with numbers in a new column. If someone could give me some pointers here. Will I need to use VBA? thanks -- JulianActon ------------------------------------------------------------------------ JulianActon's Profile: http://www.excelforum.com/member.php...o&userid=14921 View this thread: http://www.excelforum.com/showthread...hreadid=482185 |
#2
|
|||
|
|||
extracting numbers from variable text
I don't think you'll need to use VBA- depending on the way cells are
set up you can write an IF statement that only returns a numeric value. In your example Employees: 46 | Employee Growth: -4.17% is the | meant to be a column border? If yes, then try modifying your MID formula to =VALUE(MID(A1,FIND(":",A1,1)+2,LEN(A1))) .... where A1 is the Employees: cell. This will extract just the numeric portion of that cell. However, since you indicate that this formula is applied to cells that do not contain a number, you can modify it to =IF(ISNUMBER(VALUE(MID(A1,FIND(":",A1,1)+2,LEN(A1) ))),VALUE(MID(A1,FIND(":",A1,1)+2,LEN(A1))),"") You might also try an IF that applies the formula only to those cells that contain the word "Employee": =IF(ISNUMBER(FIND("Employee",A1,1)),VALUE(MID(A1,F IND(":",A1,1)+2,LEN(A1))),"") |
#3
|
|||
|
|||
extracting numbers from variable text
On Fri, 4 Nov 2005 08:07:52 -0600, JulianActon
wrote: hi I am trying to extract a numeric value from a column of text that does not always have any relevant data. Typical column entries are like this: Employees: 46 | Employee Growth: -4.17% Employees: 3336 | Employee Growth: -5.66% Employees: 1700 | Employee Growth: % but in this mixed bag of data there are many odd entries of free text e.g. Two areas are using xx. Production studio's . . . . . The data I want to extract is the number of employees, which can be anything between a 1 and 6 digit number. I can do a basic extraction using =MID(cellref, 12,6) to get the raw information, but this will not always return a number. I need to remove the junk and just be left with numbers in a new column. If someone could give me some pointers here. Will I need to use VBA? thanks If your format with regard to the Employees is always the same, then it appears as if your number of employees will always be located between the 1st and 2nd spaces in your string. That being the case, the following formula will extract that number for any number of employees: =TRIM(MID(TRIM(A1),FIND(" ",TRIM(A1)),FIND( " ",TRIM(MID(A1,FIND(" ",TRIM(A1)),1024))))) This extracts the number as TEXT. If you require that the number be numeric, then prepend a double unary. =--TRIM(MID(TRIM(A1),FIND(" ",TRIM(A1)),FIND( " ",TRIM(MID(A1,FIND(" ",TRIM(A1)),1024))))) If your entries have greater variation, then post back with more data. --ron |
#4
|
|||
|
|||
extracting numbers from variable text
Gents, many thanks for your suggestions. Have tried both solutions, both give #Value! when the data is not “Employees 12345 . . . . etc” The | is not a column border, it’s just part of the text. Am currently trying to diagnose why am still getting #value! -- JulianActon ------------------------------------------------------------------------ JulianActon's Profile: http://www.excelforum.com/member.php...o&userid=14921 View this thread: http://www.excelforum.com/showthread...hreadid=482185 |
#5
|
|||
|
|||
extracting numbers from variable text
On Fri, 4 Nov 2005 10:07:33 -0600, JulianActon
wrote: Gents, many thanks for your suggestions. Have tried both solutions, both give #Value! when the data is not “Employees 12345 . . . . etc” The | is not a column border, it’s just part of the text. Am currently trying to diagnose why am still getting #value! As I posted in my response with regard to your data format, "If your entries have greater variation, then post back with more data". You are getting the VALUE error because the data is not in the format that you posted. It's hard for us to guess at what your various formats might be. Unless you share that with us, coming up with a solution will be very difficult and time consuming. So give examples of the various formats of these entries. --ron |
#6
|
|||
|
|||
extracting numbers from variable text
Apologies. This is the contents of a standard cell: Employees: 1516 | Employee Growth: 9.38% - these cells are pretty consistent, the main variant here is Employees: 20 | Employee Growth: % ie no figure shown for the % growth, but I'm not interested in that data. The other data in this column is of varying length, from between 25 and 600 chars, and is made up of free text. Here's an example: David xxxxxxxx is no longer CIO, but Corporate Responsibility. However he will pass the mail onto the CTO Kevin and his secretary Lynn. 020 1111 4444. Or, the cell is completely empty. -- JulianActon ------------------------------------------------------------------------ JulianActon's Profile: http://www.excelforum.com/member.php...o&userid=14921 View this thread: http://www.excelforum.com/showthread...hreadid=482185 |
#7
|
|||
|
|||
extracting numbers from variable text
On Fri, 4 Nov 2005 11:21:28 -0600, JulianActon
wrote: Apologies. This is the contents of a standard cell: Employees: 1516 | Employee Growth: 9.38% - these cells are pretty consistent, the main variant here is Employees: 20 | Employee Growth: % ie no figure shown for the % growth, but I'm not interested in that data. The other data in this column is of varying length, from between 25 and 600 chars, and is made up of free text. Here's an example: David xxxxxxxx is no longer CIO, but Corporate Responsibility. However he will pass the mail onto the CTO Kevin and his secretary Lynn. 020 1111 4444. Or, the cell is completely empty. If it is TRUE that cells from which you do NOT want to extract data do NOT have "Employees: " in them, then: =IF(ISERROR(FIND("Employees: ",A1)),0, --TRIM(MID(TRIM(A1),FIND(" ",TRIM(A1)), FIND(" ",TRIM(MID(A1,FIND(" ",TRIM(A1)),1024)))))) will return zero for lines that do not contain that string. If we have to do more sophisticated pattern matching, that is possible also, but would be best done by using the morefunc.xll addin and regular expressions. --ron |
#8
|
|||
|
|||
extracting numbers from variable text
Thank you very much Ron. That works. I have learnt a lot from this. regards Julian -- JulianActon ------------------------------------------------------------------------ JulianActon's Profile: http://www.excelforum.com/member.php...o&userid=14921 View this thread: http://www.excelforum.com/showthread...hreadid=482185 |
#9
|
|||
|
|||
extracting numbers from variable text
On Sun, 6 Nov 2005 16:18:11 -0600, JulianActon
wrote: Thank you very much Ron. That works. I have learnt a lot from this. regards Julian Glad to help. Thanks for the feedback. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
text and numbers same cell and formulas still work (like lotus) | Excel Worksheet Functions | |||
How do I convert numbers stored as text with spaces to numbers | Excel Discussion (Misc queries) | |||
How to reformat numbers stored as text (apostrophe at beginning) | Excel Discussion (Misc queries) | |||
Concatenate text and numbers? | Excel Worksheet Functions | |||
Remove Numbers from text | Excel Worksheet Functions |