Is there a formula for....
 

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

Is there a formula for....
 

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

Is there a formula for....
 

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

Is there a formula for....
 

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"

Is there a formula for....
 

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"

Is there a formula for....
 

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

Is there a formula for....
 

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"

Is there a formula for....
 

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 -

Is there a formula for....
 

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"

Is there a formula for....
 

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 -

Is there a formula for....
 

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"

Is there a formula for....
 

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

Is there a formula for....
 

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


"Glenn" wrote:

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

Is there a formula for....
 

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


"Pete_UK" wrote:

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 -

Is there a formula for....
 

Like this

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

to this

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


"Glenn" wrote:

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

Is there a formula for....
 

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

Is there a formula for....
 

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

Is there a formula for....
 

Again Glen thank you for your help and your patience.

--
gueyo


"Glenn" wrote:

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