Return either a concatenated value or blank value

**n3mcx1** · 05-10-2012, 12:43 PM

Hi All,

Thanks for taking the time to assist. I am not new to Excel, but simply am having trouble sorting out this particular issue.

I have two columns X and Z in this case.

Column X contains a value derived from a lookup formula in a previous cell and either has a value or is blank.

In column Z, I need to perform a concatenation of the value with some other info, unless the cell in Column X is blank, in which case, just leave it blank.

In column Z, I currently use ="FA"&X5&"000001" (basic enough) which returns what I want, in this case FA6945000001, however my trouble is when the column X cell is blank, the value in Column Z returns FA0000001, when i need it to be blank. Ultimately, I will filter the blank columns out.

Also, if possible, I would like column Z to increment the resulting values

Once again, thanks in advance for helping with this problem,

tks,

Tim

**ron2k_1** · 05-10-2012, 12:56 PM

=if(x5="","","FA"&X5&"000001")

What do you mean increment the resulting value?

**ron2k_1** · 05-10-2012, 01:08 PM

If you meant, that the result in Z5 would end with 1 and Z6 will end with 2 (assuming that X6 is not blank). Then you'd need a helper column. Say column AA (if it's not used. If it is then use another one and change AA to whatever column actually use in the revised formulas I'll give for column Z). Then you can hide this helper Column (AA in my case)

1. In AA5, enter the number 1 (Just enter 1)
2. In AA6, =IF(X6="","",MAX(AA$5:AA5)+1)
3. Then in Z5, enter the revised formula =IF(X5="","","FA"&X5&"00000"&AA5)

**n3mcx1** · 05-10-2012, 01:17 PM

Hi Ron,

Thanks very much for the reply, however, column Z still is returning FA0000001 when column X is blank. Seems as though the blank cell in column X is being seen as a "0" instead of an actual blank cell.

Also, thanks for the helper cell. I thought of a similar scenario while eating my lunch, and that works fine.

Just still having issues with the blank cell bit.

tks,

Tim

**ron2k_1** · 05-10-2012, 01:19 PM

What do you call blank? A 0 or just Blank. What is the formula in X5. Maybe that will give me and idea as to what is returned as Blank in the cell. Nothing to worry about, all you need to do is to change the formula in Z5 to reflect what result is classified as blank in X5.

**n3mcx1** · 05-10-2012, 01:32 PM

Ron,

The formula in X5 is =LOOKUP(99^99,--("0"&MID('#40UserPilot'!P3,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},'#40UserPilot'!P3&"0123456789")),ROW($1:$10000)))), essentially stripping out the numbers from a long text string from a different worksheet, leaving only the numeric characters from that string. I know there is likely a simpler way, but it works for me for my purposes.

In the case of a cell being blank, the lookup on cell P3 on the other workbook does not contain any numeric characters, thus being blank.

Hope that is enough info for you.

tks,

Tim

**ron2k_1** · 05-10-2012, 01:48 PM

So I assume that the result will be 0, if there is no numeric value to return in X5. If that is the case, then try the following modification to the formulas I gave you before:

In AA6, =IF(X6=0,"",MAX(AA$5:AA5)+1)
IN Z5, =IF(X5=0,"","FA"&X5&"00000"&AA5)