Is there a formula for....

  • Thread starter Thread starter gueyo
  • Start date Start date
G

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
 
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
 
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"
 
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.
 
OK thanks again.
--
gueyo


Glenn said:
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"
Click to expand...
 
Sorry to bother you, but what if I have only one measurement.
From here
2-1/2

to here

2-1/2"
 
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
 
gueyo said:
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
Click to expand...


=SUBSTITUTE(SUBSTITUTE(A1," ","""W ",3)," ","""L ",1)&"""H"
 
gueyo said:
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.
Click to expand...


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 said:
Sorry to bother you, but what if I have only one measurement.
From here
2-1/2

to here

2-1/2"
Click to expand...


If you wish to combine that with the original answer:

=IF(ISERROR(FIND(" ",A1)),A1&"""",
SUBSTITUTE(SUBSTITUTE(A1," ","""W ",3)," ","""L ",1)&"""H")
 
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 said:
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.
Click to expand...


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