Results 1 to 4 of 4
  1. #1
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #2
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #3
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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?

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: build a string from single cells (Excel)

    How about:
    =if(count(A3:F3)=0,"",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

Posting Permissions

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