Concanate and Excel reading the data as Text?

[PCRIDE]

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

 

[andiemac]

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)

 

[PCRIDE]

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?

 

[PCRIDE]

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


???

 

[PCRIDE]

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.

 

[PCRIDE]

Still doesn't work

 

[Joe4]

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.

 

[PCRIDE]

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

 

[Joe4]

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!

 

[PCRIDE]

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!