Results 1 to 10 of 10
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Extract Text with space (Excel 2002)

    Hi

    I want to extract text from b4 and c4 I have tried b4&c4 but can't get a space between the two.

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extract Text with space (Excel 2002)

    <code>=B4&" "&C4</code>

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Extract Text with space (Excel 2002)

    I use Hans's method myself, as it is easier and shorter, but if you want to eliminate the fuss with syntax, you can use the CONCATENATE function. Supply it the cells with text and a space where needed.
    I.E. =CONCATENATE(B4," ",C4) . (Just spacebar in the second argument. The palette puts the quotes in automatically!)
    Regards,
    Rudi

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Extract Text with space (Excel 2002)

    Hi Hans

    Thanks to you and Rudi for your reply's

    Braddy
    If you are a fool at forty, you will always be a fool

  5. #5
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract Text with space (Excel 2002)

    If you want to be cautious (avoid extraneous spaces)...

    =TRIM(B4&" "&C4)
    Microsoft MVP - Excel

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Extract Text with space (Excel 2002)

    Hi Aladin

    Thank you for your contribution.

    Braddy
    If you are a fool at forty, you will always be a fool

  7. #7
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract Text with space (Excel 2002)

    I have this same problem, but I use a comma between quotes. Is there a way to use your code and eliminate the commas?
    When I use this code I get unnecessary commas if the cell C4 is blank. IE =TRIM(B4&", "&C4&", "&D4).

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract Text with space (Excel 2002)

    Try:

    <pre>=IF(C4="",TRIM(B4&", "&D4),TRIM(B4&", "&C4&", "&D4))
    </pre>

    Legare Coleman

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extract Text with space (Excel 2002)

    To handle all cases quickly becomes complicated:

    =TRIM(B4)&IF(OR(TRIM(B4)="",TRIM(C4&D4)=""),"",", ")&TRIM(C4)&IF(OR(TRIM(C4)="",TRIM(D4)=""),"", ", ")&TRIM(D4)

    It might be easier to create a user-defined function:

    Function Concat(ParamArray varValues())
    Dim i As Integer
    Dim strResult As String
    For i = LBound(varValues) To UBound(varValues)
    If Not Trim(varValues(i)) = "" Then
    strResult = strResult & ", " & Trim(varValues(i))
    End If
    Next i
    If Not strResult = "" Then
    strResult = Mid(strResult, 3)
    End If
    Concat = strResult
    End Function

    You can then use this formula:

    =Concat(B4,C4,D4)

    It can easily be expanded for more cells:

    =Concat(B4,D4,F4,H4,J4)

  10. #10
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract Text with space (Excel 2002)

    With just 3 cells to concatenate, you could get away with:

    =TRIM(SUBSTITUTE(IF(B4<>"",", "&B4,"")&IF(C4<>"",", "&C4,"")&IF(D4<>"",", "&D4,""),", ","",1))
    Microsoft MVP - Excel

Posting Permissions

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