Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
extracting numbers from data
Hi,
Is there a way to extract the numbers, remove them and place them in an adjacent column from the cells in the example below? The LEFT function does not do the entire trick, since the amount of numbers differs per cell and I still need to delete the number in the original cell. 1bla 1.1blabla 1.1.1blablabla 1.1.1.1blablablabla Thanks |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
extracting numbers from data
I would copy the entire column over to the next column and then use the
feature of the free add-in called ASAP Utilities, to delete all the alpha-characters in that column.......leaving only the numbers........the add-in is available at www.asap-utilities.com Vaya con Dios, Chuck, CABGx3 " wrote: Hi, Is there a way to extract the numbers, remove them and place them in an adjacent column from the cells in the example below? The LEFT function does not do the entire trick, since the amount of numbers differs per cell and I still need to delete the number in the original cell. 1bla 1.1blabla 1.1.1blablabla 1.1.1.1blablablabla Thanks |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
extracting numbers from data
Try this:
With A1 containing the text you want to parse Put this ARRAY FORMULA in B1: =LEFT(A1,LEN(A1)-MATCH(TRUE,ISNUMBER(--MID(A1,LEN(A1)+1-ROW($A$1:INDEX($A:$A,LEN(A1),1)),1)),0)+1) Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter]. Copy B1 and paste into B2 and down as far as you need Is that something you can work with? *********** Regards, Ron XL2002, WinXP " wrote: Hi, Is there a way to extract the numbers, remove them and place them in an adjacent column from the cells in the example below? The LEFT function does not do the entire trick, since the amount of numbers differs per cell and I still need to delete the number in the original cell. 1bla 1.1blabla 1.1.1blablabla 1.1.1.1blablablabla Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Manual control of link updating for downloaded quotes? | Excel Discussion (Misc queries) | |||
Two Questions: R1C1 and stop text number converting to data | Excel Discussion (Misc queries) | |||
Extracting Comma Seperated data into individual Cells | Excel Discussion (Misc queries) | |||
ranking query | Excel Discussion (Misc queries) | |||
Match Last Occurrence of two numbers and Count to Previous Occurence | Excel Worksheet Functions |