Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Concanate and Excel reading the data as Text?

This is a discussion on Concanate and Excel reading the data as Text? within the Excel Questions forums, part of the Question Forums category; Hi, I have a strange one. Here is my formula =IF(LEFT(B32,1)="6",CONCATENATE(L32,K32,B32),B32) basically it looks at the first character in a ...

  1. #1
    Board Regular
    Join Date
    Jan 2008
    Posts
    711

    Default Concanate and Excel reading the data as Text?

    Hi, I have a strange one.

    Here is my formula

    =IF(LEFT(B32,1)="6",CONCATENATE(L32,K32,B32),B32)

    basically it looks at the first character in a cell, if it begins with 6, then Concatenate 2 zeros in front of it L and K, otherwise return the number in the Cell B32.

    The reason I have to use 2 helper cells for L and K for Zeros, is because excel won't display 2 leading zeros when using them in a formula like in this example
    =IF(LEFT(B32,1)="6","00"&B32),B32)

    So now when I do a CTRL F and search for the text 13028196, excel won't find it
    - Keep in mind this number is generated by the formula up top in the first example above. If I do a compare, excel see's it as TRUE.

    So Excel says both numbers match. CTRL F doesn't find it. Therfore my VLOOKUP fails because excel can't find it!!!!!

  2. #2
    Board Regular
    Join Date
    Oct 2009
    Location
    Washington
    Posts
    215

    Default Re: Concanate and Excel reading the data as Text?

    Are you trying to get all your numbers to be text?

    =IF(LEFT(B32,1)="6","00"&TEXT(B32,"0"),TEXT(B32,"0"))

    Or just the ones like "006***"?

    =IF(LEFT(B32,1)="6","00"&TEXT(B32,"0"),B32)

  3. #3
    Board Regular
    Join Date
    Jan 2008
    Posts
    711

    Default Re: Concanate and Excel reading the data as Text?

    I tried this and excel still cannot find the number using a VLOOKUP or this TEXT formula. Some numbers get picked up in the VLOOKUP, but others don't. CTRL F doesn't find the text in a VLOOKUP formuala but does in a regular text field.

    Its like the formula is making it not readable by excel.

    Any ideas?

  4. #4
    Board Regular
    Join Date
    Jan 2008
    Posts
    711

    Default Re: Concanate and Excel reading the data as Text?

    So Here is a more clear explaination.

    I have a sheet that performs Vlookups. In one cell I have the number 13028196, then I use the text formula above and it populates that number again in another cell because it doesn't begin with 6.

    When I do a CTRL F on the regular text cell, Excel finds it, when I do the CTRL F on the cell that returns the formula using the TEXT formula above, Excel CAN"T find it.

    If I do a =A1=S1, it comes back TRUE
    I copied the format of the text cell to the formula cell and still no go


    Why is the formula making the number not work with a VLOOKUP or CTRL F??

    Also I just noticed that the only numbers that are getting "looked Up" is the ones that begin with 6, but with the doublezero in front.

    Why is this??

    610660-0221 gets converted to 00610660-0221 using the FOrmula (Adds 2 zeros in front)
    A CTRL F and Vlookup seems to work fine

    These numbers should be found in the VLOOKUP and CTRL but they are not when they are processed through the formula above.

    1115108
    13028280
    1115073


    ???
    Last edited by PCRIDE; Aug 24th, 2012 at 03:56 PM.

  5. #5
    Board Regular
    Join Date
    Jan 2008
    Posts
    711

    Default Re: Concanate and Excel reading the data as Text?

    It appears most of these numbers in the raw data are stored as TEXT

    1115108
    13028280
    1115073



    The 006 numbers are Numbers in Excel. That may be the issue. Stand by.

  6. #6
    Board Regular
    Join Date
    Jan 2008
    Posts
    711

    Default Re: Concanate and Excel reading the data as Text?

    Still doesn't work

  7. #7
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    25,887

    Default Re: Concanate and Excel reading the data as Text?

    I sure wish I could see your sheet to see what you are looking at, but here are a few tips that may help:

    1. For a VLOOKUP to work, the two values that you are comparing MUST be the same data type (both Text or both Numeric). You can use the ISNUMBER function to verify if your entries are numeric or not.

    2. Just because a value displays will leading zeroes when you look at it doesn't mean it really has leading zeroes in it. Custom Formats can be applied to have them displayed even though they really aren't part of the actual entry. The easiest way to tell is to select that cell and see what shows up in the Formula bar. Also note that anything entered as a number (and not text) will drop leading zeroes.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  8. #8
    Board Regular
    Join Date
    Jan 2008
    Posts
    711

    Default Re: Concanate and Excel reading the data as Text?

    Looks like I got it resolved, and I was able to use my first formula. The problem was that some numbers were stored as Text, others as Numbers. This is why some were not reporting in the VLOOKUP.

    I just highlighted the entire column in Excel, clicked the drop down where Excel tells me the numbers are stored as Text. I changed this to Numbers and everything works.

    What a pain this was to troubleshoot!!!!

    Note to selft: Make sure all your data is the same format!!!

    Thanks for all the help everyone.
    PCRIDE

  9. #9
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    25,887

    Default Re: Concanate and Excel reading the data as Text?

    You must have not seem my previous post!
    1. For a VLOOKUP to work, the two values that you are comparing MUST be the same data type (both Text or both Numeric). You can use the ISNUMBER function to verify if your entries are numeric or not.
    Glad you figured it out anyway!
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  10. #10
    Board Regular
    Join Date
    Jan 2008
    Posts
    711

    Default Re: Concanate and Excel reading the data as Text?

    Thanks Joe4, I actually did see but took it the wrong way. I formatted the cells the Same, however I escaped the actual values in the Cells were not!! It took me a few times to isolate the difference!

    Thanks again to All of you for helping out!

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com