Add a trailing space

[30136353]

Hi Guys,

I have a piece of code which looks like this in order to remove all spaces within text:

ActiveSheet.Range("K1:L5000").Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart

I then need to use the same selection and add in a trailing space back onto the end of the text?

Any idea?

Thanks

 

[Joe4]

Does your range consist of text or numbers (or both)?
Are there any blank cells in that range?
Do you want add a single space to those as well?

I am curious, if you don't mind me asking, why do you need to do this?
It just seems like a rather odd task.

 

[30136353]

Does your range consist of text or numbers (or both)?
Are there any blank cells in that range?
Do you want add a single space to those as well?

I am curious, if you don't mind me asking, why do you need to do this?
It just seems like a rather odd task.

It is mixture of text + numbers, there is a lot of blank cells within, and yes adding a single space to those at the end will not matter. I am removing all spaces within the text to remove any anomilies for a lookup formula, but then adding a space into the end gives me a more unique lookup also.

I'E my match maybe index(a,Match(*123 *), instead of searching for *123*. Because the 2nd match will find 1234 etc if it comes first... hard to explain, but method behind the madness... Thanks

 

[Joe4]

I do not know of a good way to add a single space to the end of each cell without looping through each one.
Try this:

Sub MyMacro()

    Dim cell As Range
    Dim rng As Range
    
    Set rng = ActiveSheet.Range("K1:L5000")

    Application.ScreenUpdating = False

'   Remove all spaces
    rng.Replace What:=" ", Replacement:="", LookAt:=xlPart

'   Add space to end of each cell
    For Each cell In rng
        cell = cell & " "
    Next cell

    Application.ScreenUpdating = True

End Sub

 

[Fluff]

Why not just use
MATCH(*123,range
That way it only looks at the end of the value, so no need for the space.

 

[30136353]

Why not just use
MATCH(*123,range
That way it only looks at the end of the value, so no need for the space.

Fluff, brilliant! I didn't understand that's how it worked, that's just solved all my problems! Thanks also Joe for the help.

 

[Fluff]

Glad we could help & thanks for the feedback

 

[Joe4]

I was already working on my code before your reply, and Fluff's suggestion. His is the way to go, as it saves you from unnecessarily looping!