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

T

trav2016

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
 
B

Bob Phillips

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
B

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
 
P

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
 
P

pinmaster

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
 
K

Kevin B

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)
 
R

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

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
 
Top