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

--
gueyo

gueyo wrote:
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"

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


"Glenn" wrote:

gueyo wrote:
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"

gueyo wrote:
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"

OK thanks again.
--
gueyo


"Glenn" wrote:

gueyo wrote:
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"

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

to here

2-1/2"
--
gueyo


"Glenn" wrote:

gueyo wrote:
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"

In this case you can just do:

=A1 & CHAR(34)

Hope this helps.

Pete

On Apr 23, 5:24*pm, gueyo wrote:
Sorry to bother you, but what if I have only one measurement.
From here
2-1/2

to here

2-1/2"
--
gueyo



"Glenn" wrote:
gueyo wrote:
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"- Hide quoted text -

- Show quoted text -

gueyo wrote:
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")

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

On Apr 23, 4:19*pm, gueyo wrote:
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

--
gueyo

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

Thank you.
--
gueyo


"Pete_UK" wrote:

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

On Apr 23, 4:19 pm, gueyo wrote:
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

--
gueyo

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

On Apr 23, 5:19*pm, gueyo wrote:
This formula gave me an error. I'm not familiar enough to find it.

Thank you.
--
gueyo



"Pete_UK" wrote:
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

On Apr 23, 4:19 pm, gueyo wrote:
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

--
gueyo- Hide quoted text -

- Show quoted text -

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"
--
David Biddulph

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

--
gueyo