Adding spaces to data within a cell

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

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