split "05-09-10-17-24" into indivdual cell

[Guest]

I want to split "05-09-10-17-24" into indivdual cell for example "05" "09"
"10" "17" "24" of courace with out the quatations

 

[Bob Phillips]

Menu Data>Text To Columns, with a delimiter of -

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[bob777]

if cell A1 contains 45-34-77

then mid(A1,4,2) returns 34

if cell A1 contains "45-34-77"

then mid(A1,5,2) returns 3

 

[Pete]

Just do Data | Text-to-Columns, specifying the hyphen as the delimiter.

Alternatively, you can use a formula with MID in each of the five
columns, eg if data is in A1:

B1: =MID(A1,1,2)
C1: =MID(A1,4,2)
D1: =MID(A1,7,2)
E1: =MID(A1,10,2)
F1: =MID(A1,13,2)

Hope this helps.

Pete

 

[Guest]

Hi, select your data then go to Data/Text to Columns, make sure "Dilimited"
is selected above, hit NEXT, select "Other" and enter a dash into the box,
hit "Finish".

Make sure you have enough free columns on the right before doing this.

HTH
JG

 

[Guest]

The following formulas assume that the value 05-09-10-17-24 is located in
Cell A1, and the formulas are placed in B1, C1, D1, E1 & F1:

Cell B1 Formula:
=LEFT(A1,2)

Cell C1 Formula:
=MID(A1,4,2)

Cell D1 Formula:
=MID(A1,7,2)

Cell E1 Formula:
=MID(A1,10,2)

Cell F1 Formula:
=RIGHT(A1,2)

 

[Ron Rosenfeld]

I want to split "05-09-10-17-24" into indivdual cell for example "05" "09"
"10" "17" "24" of courace with out the quatations

It's certainly simple to use the Data/Text to Columns wizard as others have
suggested.

But for a dynamic solution which can handle any length of number in your
string, you could use a regular expression formula.

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

Then, with your string in A1, enter this formula in B1 and copy/drag across as
far as required:

=REGEX.MID($A1,"\d+",COLUMNS($A:A))
--ron

 

[Guest]

Thanks, Pete it worked perfect.