Split the numbers in a alpha numeric case

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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 said:
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
Click to expand...

Use Data->Text to columns..., select delimited and specify space and hyphen
as delimeters.
 
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.
 
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
 
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
Click to expand...

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
 
Back
Top