+ Reply to Thread
Results 1 to 21 of 21

Equitation to Text issue

  1. #1
    Forum Contributor
    Join Date
    02-17-2013
    Location
    israel
    MS-Off Ver
    Excel 2003
    Posts
    106

    Unhappy 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


  2. #2
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Equitation to Text issue

    Did you try using Left, Mid or Right functions?

    =mid(a1,1,5) ...??
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Forum Contributor
    Join Date
    02-17-2013
    Location
    israel
    MS-Off Ver
    Excel 2003
    Posts
    106

    Re: Equitation to Text issue

    Thanks,

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

  4. #4
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Equitation to Text issue

    Perhaps if we have some data with expected results in a sample workbook..

  5. #5
    Forum Contributor
    Join Date
    02-17-2013
    Location
    israel
    MS-Off Ver
    Excel 2003
    Posts
    106

    Re: Equitation to Text issue

    ok see attached
    Attached Files Attached Files

  6. #6
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: Equitation to Text issue

    Try this
    With your data in A2 down
    In B2, drag across to D2
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  7. #7
    Forum Contributor
    Join Date
    02-17-2013
    Location
    israel
    MS-Off Ver
    Excel 2003
    Posts
    106

    Re: Equitation to Text issue

    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 !!!!

  8. #8
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: Equitation to Text issue

    Try
    In FF4
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    In FI4
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    In FL4
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by Marcol; 05-22-2013 at 08:24 AM.

  9. #9
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: Equitation to Text issue

    You might be safer with this in case the "X" could be lower case
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    See also Post #8

  10. #10
    Forum Contributor
    Join Date
    02-17-2013
    Location
    israel
    MS-Off Ver
    Excel 2003
    Posts
    106

    Re: Equitation to Text issue

    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"

  11. #11
    Forum Contributor
    Join Date
    02-17-2013
    Location
    israel
    MS-Off Ver
    Excel 2003
    Posts
    106

    Re: Equitation to Text issue

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

  12. #12
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: Equitation to Text issue

    This is getting very messy.

    If you have more exceptions to the rules so far, post a sample workbook showing what you need for each exception, and where the results should be shown.

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

    See this workbook

    Are you still using 2003?
    Attached Files Attached Files

  13. #13
    Forum Contributor
    Join Date
    02-17-2013
    Location
    israel
    MS-Off Ver
    Excel 2003
    Posts
    106

    Re: Equitation to Text issue

    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
    Attached Files Attached Files

  14. #14
    Forum Contributor
    Join Date
    02-17-2013
    Location
    israel
    MS-Off Ver
    Excel 2003
    Posts
    106

    Re: Equitation to Text issue

    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

  15. #15
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    23,144

    Re: Equitation to Text issue

    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

  16. #16
    Forum Contributor
    Join Date
    02-17-2013
    Location
    israel
    MS-Off Ver
    Excel 2003
    Posts
    106

    Re: Equitation to Text issue

    Pete,

    Your a life saver.

    Thank you so much !!!!

    It worked !!!!!

  17. #17
    Forum Contributor
    Join Date
    02-17-2013
    Location
    israel
    MS-Off Ver
    Excel 2003
    Posts
    106

    Re: Equitation to Text issue

    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!!!!!!

  18. #18
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    23,144

    Re: Equitation to Text issue

    Yes, upload another example.

    Note Marcol's point:

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

  19. #19
    Forum Contributor
    Join Date
    02-17-2013
    Location
    israel
    MS-Off Ver
    Excel 2003
    Posts
    106

    Re: Equitation to Text issue

    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
    Attached Files Attached Files

  20. #20
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    23,144

    Re: Equitation to Text issue

    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

  21. #21
    Forum Contributor
    Join Date
    02-17-2013
    Location
    israel
    MS-Off Ver
    Excel 2003
    Posts
    106

    Re: Equitation to Text issue

    Pete,

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

    Untill next time

    Thanks and have a great day

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1