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

Menu DataText To Columns, with a delimiter of -

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"trav2016" wrote in message
...
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

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 34


--
bob777
------------------------------------------------------------------------
bob777's Profile: http://www.excelforum.com/member.php...o&userid=28504
View this thread: http://www.excelforum.com/showthread...hreadid=506319

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

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

"trav2016" wrote:

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

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)

--
Kevin Backmann


"trav2016" wrote:

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

On Mon, 30 Jan 2006 05:00:28 -0800, "trav2016"
wrote:

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

Thanks, Pete it worked perfect.

"Pete" wrote:

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