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

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

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.

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

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

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