Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split the numbers in a alpha numeric case
I have the following Number format in one row
132-45-69 N. I want to split the numbers in three different rows like 132 in one row,45 in another and 69 in the third row. Can this be done in excell |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split the numbers in a alpha numeric case
Make sure there are a few blank rows just to the right of our column with the
strings, then highlight that column and do Data TextToColumns Delimited and set the hyphen as your delimiter.........post back if you have any trouble, or more questions......... Vaya con Dios, Chuck, CABGx3 "Jp" wrote: I have the following Number format in one row 132-45-69 N. I want to split the numbers in three different rows like 132 in one row,45 in another and 69 in the third row. Can this be done in excell |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split the numbers in a alpha numeric case
Jp wrote:
I have the following Number format in one row 132-45-69 N. I want to split the numbers in three different rows like 132 in one row,45 in another and 69 in the third row. Can this be done in excell Use Data-Text to columns..., select delimited and specify space and hyphen as delimeters. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split the numbers in a alpha numeric case
Can this be done automatically .I mean once i enter the format i want my
results split up instead of every time using the Text to colum. "CLR" wrote: Make sure there are a few blank rows just to the right of our column with the strings, then highlight that column and do Data TextToColumns Delimited and set the hyphen as your delimiter.........post back if you have any trouble, or more questions......... Vaya con Dios, Chuck, CABGx3 "Jp" wrote: I have the following Number format in one row 132-45-69 N. I want to split the numbers in three different rows like 132 in one row,45 in another and 69 in the third row. Can this be done in excell |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split the numbers in a alpha numeric case
Sure, here's a macro but it was done in XL97, you may have to Edit and/or
record one of your own to work in yo0ur version of XL. Sub AutoTextToColumns() Columns("B:D").Select Selection.Insert Shift:=xlToRight Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _ Semicolon:=False, Comma:=False, Space:=True, Other:=True, OtherChar:= _ "-", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)) Range("A1").Select End Sub Vaya con Dios, Chuck, CABGx3 "Jp" wrote: Can this be done automatically .I mean once i enter the format i want my results split up instead of every time using the Text to colum. "CLR" wrote: Make sure there are a few blank rows just to the right of our column with the strings, then highlight that column and do Data TextToColumns Delimited and set the hyphen as your delimiter.........post back if you have any trouble, or more questions......... Vaya con Dios, Chuck, CABGx3 "Jp" wrote: I have the following Number format in one row 132-45-69 N. I want to split the numbers in three different rows like 132 in one row,45 in another and 69 in the third row. Can this be done in excell |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split the numbers in a alpha numeric case
On Fri, 7 Apr 2006 10:33:03 -0700, Jp wrote:
I have the following Number format in one row 132-45-69 N. I want to split the numbers in three different rows like 132 in one row,45 in another and 69 in the third row. Can this be done in excell To do it automatically, you could use the following formulas: A2: =LEFT(A$1,FIND("-",A$1)-1) A3: =MID(A$1,FIND("-",A$1)+1,FIND("-",A$1,FIND("-",A$1)+1)-FIND("-",A$1)-1) A4: =MID(A$1,FIND(CHAR(1),SUBSTITUTE(A$1,"-",CHAR(1),2))+1, FIND(" ",A$1&" ")-FIND(CHAR(1),SUBSTITUTE(A$1,"-",CHAR(1),2))) OR you could download and install Longre's free morefunc.xll add-in from http://xcell05.free.fr enter: A2: =REGEX.MID(A$1,"\d+",ROWS($1:1)) and copy/drag down to A4. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to use spellnumber formula | Excel Worksheet Functions | |||
Currency to Text | Excel Worksheet Functions | |||
VBA | Excel Worksheet Functions | |||
VBA Code | Excel Discussion (Misc queries) | |||
Loop for VBA code? | Excel Worksheet Functions |