# Thread: build a string from single cells (Excel)

1. ## Re: build a string from single cells (Excel)

<P ID="edit" class=small>(Edited by sdckapr on 11-Jul-05 12:23. Edited formula, forgot about blank Col A...)</P>How about in G3:
=if(isnumber(a3),TEXT(A3,"000"),"100")&"-"&TEXT(B3,"00")&"-"&TEXT(C3,"00")&"-"&TEXT(D3,"000")&"-"&TEXT(E3,"00")&"W"&IF(ISNUMBER(F3),TEXT(F3,"0")&" M",F3)

Steve

2. ## Re: build a string from single cells (Excel)

Yes that works, Thanks

What do I do if for blank rows, if I wanna keep them blank?

3. ## build a string from single cells (Excel)

Edited by HansV to provide link to post - see <!help=19>Help 19<!/help>

Hello All, I am following up one of my posts <post#=482832>post 482832</post#>. I was trying to make a string consistent by using a macro. This was difficult because of all the different scenerio and I thank Steve, Legare, and Hans for there help on it. I have spent more time on the problem and ended up using delimit to break the string down.

I have attach a work sheet of this. I am wondering if anyone knows how I can put this string back together following some guidelines

Column A the values are 100 if the cell is blank, if not blank leave the number alone.
Column B has two number positions, 00, but if there is a number in the cell for example 5 the cell would be 05
Column C is the same as B
Column D has three number positions, 000, but if there is a number for example 43 then the cell would be 043
column E is the same as column B,C
and
Column F should start with a W,number,M so for example 4 would become W4M

I have been trying this little formula, but cant seem to make it work

=Text((IF(A1=" ")THEN("100")Else(A1&"000"&"-")),(B2&"00"&"-"),(C1&"00"&"-"),(D1&"000"&"-"),(E1&"00"&"W"),(F1&"M"))

Anyone have a better idea or correction to this formula?