Equitation to Text issue

Good morning all,

I have in one cell 5.5X5.8X3.7 and i need it to split into three cells i know that the text to column feature works great, if only this was standard text, the issue that i am having is that this info is being brought in via a Vlookup. is there an equation that exists that i can make this Vlookup into text?

Any help would be much appreciated

Thanks,

I did the issue with that is that some figure have two places after the " . " and some have three

ok see attached

Great that works!

but how do i do that if i need to skip columns in the middle meaning my original value is in H4 and i need the first value to be in FF4 the second value to be in FI4 and third value in FL4. keeping in mind there are other values in the cells between.

Thanks !!!!

Marcol,

Thanks again great work.

One more issue hopefully you have a quick answer up your sleeve. some of my values in column H are only one or two values not three and more so some of the values are (and need to stay) like this "4.95-4.99X2.83"

As well there are some columns that have a value of "0" (or 0) in them. is there any way around this ?

You are correct and i am sorry for the mess these are all issues that are coming up "on the fly" as i am working through and implementing your information

and one more that i forgot to add to the spread sheet if the value in H4 is 0 then put 0 in all of the subsequent columns

Again i am really sorry for the mess and thank you very much for the help

Use these formulae in the cells stated:

FF4: =IF(ISNUMBER(FIND("-",H4)),TRIM(MID(SUBSTITUTE("X"&SUBSTITUTE($H4,"x","X"),"X",REPT(" ",255)),1*255,255)),MID(SUBSTITUTE("X"&SUBSTITUTE($H4,"x","X"),"X",REPT(" ",255)),1*255,255)*1)

FI4: =IF(ISNUMBER(SEARCH("x",H4)),MID(SUBSTITUTE("X"&SUBSTITUTE($H4,"x","X"),"X",REPT(" ",255)),2*255,255)*1,"")

FL4: =IF(LEN(H4)-LEN(SUBSTITUTE(SUBSTITUTE(H4,"x",""),"X",""))<2,"",IF(ISNUMBER(FIND("-",H4)),"",MID(SUBSTITUTE("X"&SUBSTITUTE($H4,"x","X"),"X",REPT(" ",255)),3*255,255)*1))

Then copy down as required.

Hope this helps.

Pete

Pete,

Your a life saver.

Thank you so much !!!!

It worked !!!!!

Pete,

It worked thanks a ton !!!

I ran into one issue however and it is my fault that i didnt check it.. the first column cannot contain x.xx-x.xx as i though it could. please could you re-write the formula for FF4 to take only the number before the "-" disregard the number after the dash and the number after the "X" in the second column ?

I hope that i explained my self clearly if not i would be happy to upload an example.

Thanks again!!!!!!

Yes, upload another example.

Note Marcol's point:

Best not to drip feed conditions, it tends to put people off helping.

Pete

Pete,

duely noted and i appologize again i cant know what is going to cause the errors (or issues) untill i try uploading the files with the formulas to the sites that need it....

That is why i "drip conditions" and i do really appriciate the help.

See attached

Try this one in FF4:

=IF(ISNUMBER(FIND("-",H4)),LEFT(H4,FIND("-",H4)-1)*1,MID(SUBSTITUTE("X"&SUBSTITUTE($H4,"x","X"),"X",REPT(" ",255)),1*255,255)*1)

The other two formulae should remain the same.

Hope this helps.

Pete

Pete,

Thanks you really are a life save it seems to be working.

Untill next time

Thanks and have a great day