# Adding spaces to data within a cell

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

XXX-5RM
XXX-5ARM
XXX-5CARM
XXX-55RM
XXX-55ARM
XXX-55CARM
XXX-555RM
XXX-555ARM
XXX-555CARM

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
******************************

• Thanks. I'm not that strong in Excel to understand the functions, but appreciate your response.

• "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.

• via formula :

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(G4,"X-5","X 5",1),"5R","5 R",1),"5A","5 A",1),"5C","5 C",1)

• 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.
=MID(A1,5,10)

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

• Hi Iperson,

Chris's solution is an excellent one. However this would only work if your data only has 5s.

I have attached another solution that will also work for other numbers.

Hope this helps.

Thanks,
Rennie

## Participate now!

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