Splitting Cells 1

[Topcat04]

Hi all

I have just inherited a sheet with dimensions of boxes for a complete product list.
The challenge I have is that the dimensions for Length, Width and Height are all contained in one cell, rather than in individual cells.

EG :: 6.5"x17"x4.25"

whereas I need them in seperate cells EG ::

Cell C1 would contain 6.5
Cell D1 would contain 17
Cell E1 would contain 4.25

There is no formatting to the numbers with decimal points, but they are all in the same style eg L"xW"xH"

Is there a way of pull the numbers before or after the occurrence of " ?

Your help will be appreciated as with over 1000 lines, I hope there is a way to do this rather than manually split them out.

Thanks,

 

[xlbo]

Need to use a combination of LEFT / MID & FIND fucntions

1: =VALUE(LEFT($A1,FIND("""",$A1)-1))
D1: =VALUE(MID(A1,FIND("x",A1)+1,FIND("""",$A1,FIND("""",A1)+1)-FIND("x",A1)-1))
E1: =VALUE(MID(A1,FIND("x",A1,FIND("x",A1)+1)+1,LEN(A1)-1-FIND("x",A1,FIND("x",A1)+1)))

...some clever sod will probably now post a much simpler way of doing it!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Topcat04]

Thanks Xlbo, that works for me!
Be interesting to see if someone knows a different way...

 

[GlennUK]

Well, you could do an Edit/Replace of " with nothing to leave just the x's as the separators, and then do Data/Text To Columns/Delimited/Other:x/Finish to split the data in one go.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[SkipVought]

You could also use Data > Text to columns > DELIMITER > and use the x as the DELIMITER.

I would also Edit > Replace " with NOTHING.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GlennUK]

Yeah Skip, I beat you this time, ha ha!!!

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[GlennUK]

... should have added "Great minds think alike"! :-D

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[SkipVought]

Drates! Foiled again!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Grate minds clash; as iron sharpen iron.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}