Adding spaces to data within a cell

  • I have a bunch of data that looks like this:


    I have figured out how to put a space after the prefix (XXX) by replacing the "-" with a " ". How can I add a space before the suffix (RM, ARM, CARM)? Or is there a way of breaking out the suffix into a separate column?

  • I couldn't figure out how to do it with a cell function. There doesn't seem to be a search from the right function.

    Solution 1 would be to create a search or replace from the right function and use that.

    Solution 2 is a custom function, such as below.

    Hope this helps.

    Function InjectSpaces(strOLDTEXT As String)
    Dim strPIECES(3) As String
    intLength = Len(strOLDTEXT)

    intLAST5POS = InStrRev(strOLDTEXT, "5")
    intDASHPOS = InStr(strOLDTEXT, "-")
    strPIECES(1) = Left(strOLDTEXT, intDASHPOS - 1)
    strPIECES(2) = Mid(strOLDTEXT, intDASHPOS + 1, intLAST5POS - intDASHPOS)
    strPIECES(3) = Right(strOLDTEXT, intLength - intLAST5POS)

    strNEWSTRING = Join(strPIECES, " ")

    InjectSpaces = strNEWSTRING
    End Function

  • "Text to Columns" in the Data menu seems to work for the data I have. Though it is a multistep process, it is much quicker than doing it manually.

    I found the answer on another bulletin board that I posed the question to.

    Thanks again for your help.:flower:

  • Looks like your suffix always starts in the fifth position. If so, then for example if xxx-5RM is in A1, put the following formula in A2.

    This will bring back 5RM

    By extending it to 10, it will pick up anything from position 5 to 10 from cell 1. Just copy formula down.
    Good luck,
    Terry :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!