Trimming non-alphanumeric characters instead of spaces (LTrim/RTrim)

**Sophie.Durrant** · 01-15-2015, 12:25 PM

Hello,

I am a VBA beginner. I am trying to take out any non-alphanumberic characters from a text string but only if they are at the beginning or the end of the string.

I tried LTrim and RTrim but it seems that only works for spaces, so I am wondering if there is some thing similar to do what I need?

Thanks,

Soph

**Richard Buttrey** · 01-15-2015, 12:32 PM

Hi,

Is there a definitive list of the non alphanumeric characters you're interested in?
Can these characters exist within the string.
Are there any delimiting characters (like say a space) which identifies the start or end of the alphanumeric chars.?

Would you upload a sample workbook that contains examples of typical strings. This often helps.

**Sophie.Durrant** · 01-15-2015, 01:08 PM

I can't work out how to upload a sample sorry, I'll try to explain best I can...

What I have currently is a list of names and addresses with the full address in one cell and the city, state, postcode and country are then alongside in separate cells. Because I already have city, state etc. separate I can use a SUBSTITITE function to take those out, leaving me with just the first part of the address and then I want to split that into two cells (Address1 and Address2).

Sometimes the address which is all in one cell starts or ends with a blank line so I first want to strip out char(10) but only at the beginning and end of the string. After that I want to have Address1 as being everything to the left of the first char(10) and Address2 as everything to the right and replacing char(10) with a comma.

Does my waffle make sense???

Thanks,