Is there a formula for....

[gueyo]

I have an excel sheet that has a series of measurements in which the inch
marks have been left out. How do I get from here

2-1/16 x 6-3/8 x 3-1/2
2-7/16 x 7-3/4 x 5-1/2
2-1/16 x 7-3/4 x 5-1/8
3-1/4 x 9-1/4 x 6
3 x 11-1/2 x 7-1/2

to Here

2-1/16"L x 6-3/8"W x 3-1/2"H
2-7/16"L x 7-3/4"W x 5-1/2"H
2-1/16"L x 7-3/4"W x 5-1/8"H
3-1/4"L x 9-1/4"W x 6"H
3"L x 11-1/2"W x 7-1/2"H

 

[Pete_UK]

Assuming those start in A1, put this in B1:

=LEFT(A1,SEARCH(" x",A1)-1)&CHAR(34)&"L x"&LEFT(RIGHT(A1,LEN(A1)-SEARCH
(" x",A1)-1),SEARCH(" x",RIGHT(A1,LEN(A1)-SEARCH(" x",A1)-1))-1)&CHAR
(34)&"W x"&RIGHT(A1,LEN(A1)-SEARCH(" x",A1,SEARCH(" x",A1)+1)-1)&CHAR
(34)&"H"

and copy down as far as you need to.

Hope this helps.

Pete

 

[David Biddulph]

One way would be to use Data/ Text to Columns with space x space as
delimiter. Set the column format to skip for the x columns, and to text for
the 3 separate components.
In some respects it would be better to leave the components separate, but if
you want to glue back together in the format you suggest, use
=A1&"""L x "&B1&"""W x "&C1&"""H"

 

[gueyo]

This is great. It worked wonderfully. I'm new so I don't know how to rate
this post.???
One more question please. When I dothis formula there is not a space between
the "L", "W" and the "x" is there a way to add a space? in the formula?

Thank you again.

 

[gueyo]

OK thanks again.
--
gueyo

Should be one already. If you select and copy the formula from the post, paste
it into Excel and then change the cell reference (the A1) as appropriate, there
will be a space between them. Always better to copy a formula than trying to
re-type it.

=SUBSTITUTE(SUBSTITUTE(A1," ","""W ",3)," ","""L ",1)&"""H"

 

[gueyo]

This formula gave me an error. I'm not familiar enough to find it.

Thank you.

 

[gueyo]

Sorry to bother you, but what if I have only one measurement.
From here
2-1/2

to here

2-1/2"

 

[Pete_UK]

It worked for me on your sample. It might be because of spurious line-
breaks on the newsgroups - it should be one continuous formula.

Pete

 

[Glenn]

I have an excel sheet that has a series of measurements in which the inch
marks have been left out. How do I get from here

2-1/16 x 6-3/8 x 3-1/2
2-7/16 x 7-3/4 x 5-1/2
2-1/16 x 7-3/4 x 5-1/8
3-1/4 x 9-1/4 x 6
3 x 11-1/2 x 7-1/2

to Here

2-1/16"L x 6-3/8"W x 3-1/2"H
2-7/16"L x 7-3/4"W x 5-1/2"H
2-1/16"L x 7-3/4"W x 5-1/8"H
3-1/4"L x 9-1/4"W x 6"H
3"L x 11-1/2"W x 7-1/2"H

=SUBSTITUTE(SUBSTITUTE(A1," ","""W ",3)," ","""L ",1)&"""H"

 

[Pete_UK]

In this case you can just do:

=A1 & CHAR(34)

Hope this helps.

Pete

 

[Glenn]

This is great. It worked wonderfully. I'm new so I don't know how to rate
this post.???
One more question please. When I do this formula there is not a space between
the "L", "W" and the "x" is there a way to add a space? in the formula?

Thank you again.

Should be one already. If you select and copy the formula from the post, paste
it into Excel and then change the cell reference (the A1) as appropriate, there
will be a space between them. Always better to copy a formula than trying to
re-type it.

=SUBSTITUTE(SUBSTITUTE(A1," ","""W ",3)," ","""L ",1)&"""H"

 

[Glenn]

Sorry to bother you, but what if I have only one measurement.
From here
2-1/2

to here

2-1/2"

If you wish to combine that with the original answer:

=IF(ISERROR(FIND(" ",A1)),A1&"""",
SUBSTITUTE(SUBSTITUTE(A1," ","""W ",3)," ","""L ",1)&"""H")

 

[gueyo]

Again this is excellent and worked great. Of course I have another question.
Can I change the inch mark in the first deminsion into a foot mark? I tried
but would not work.

 

[gueyo]

Thank you, Thank you
this also worked. Now I have options. :>)

 

[gueyo]

Like this

From here
2-1/2 x 3-1/2

to this

2-1/2'L x 3-1/2"W

 

[Glenn]

Again this is excellent and worked great. Of course I have another question.
Can I change the inch mark in the first deminsion into a foot mark? I tried
but would not work.

Not sure I understand. Post a "before and after" and I'll take a look.

 

[Glenn]

Like this

From here
2-1/2 x 3-1/2

to this

2-1/2'L x 3-1/2"W

=SUBSTITUTE(A1," ","'L ",1)&"""W"

 

[gueyo]

Again Glen thank you for your help and your patience.