Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Removing # from beginning of string in a column, where # is not always in the cell

0

Hi:

I'm trying to clean addresses. Very occassionally, the address is written #123 Main St in the primary address field.  The vast majority of the time, the address field is 123 Main st.  Sometimes, the address field contains secondary address information too such as 123 Main st # 456. For the third case, I remove the # 456 from the field to a new field by finding and replacing then using text to columns.  For instance, find space#space (space = one press of space bar) repalce with ,#space.Then using text to cloumns, I make delimitor , and click OKAY.  Problem is, if there is a #123 Main st, the entire entry would get moved to the second column, which would leave this primary address field NULL. So, I need a function to run first that removes only the # and the space immediately after it (if there is one) from the left most position in the string before separating the #456 type of information from the right end of the string.

Anyone have a solution?

Answer
Discuss

Discussion

How about...

=if(left(a1)="#",right(a1,len(a1)-1),a1)
gebobs (rep: 30) Jun 8, '17 at 9:24 am
Thanks. So if I apply this to a column, I first highlight the column, then in the first cell, I place this in the formula bar, substituting the column letter for a?

I've never actually used this type of programming in Excel before. Once the formula is in the formula line, How do I get it to run for the entire column, which could be a million records long?
Mitchell P Jun 8, '17 at 9:52 am
gebobs, don't forget to put that as an answer.
don (rep: 1989) Jun 8, '17 at 9:53 am
Tried to do what I suggested wiht your formula and got eror 508 in first row of the colum I am trying to do this on.  the # left most character of row 8 incolumn c, did not change.  Used  c1:c11 in range field and repalced a with c in formula bar.

I treid again using the wizard's suggestion and got FALSE raher than removed # and moved all other charcers left one space.

OAN: How do I put this in answer? When looked for answer, saw this discussion not in answer and another answer that won't work in an answer I could choose
Mitchell P Jun 8, '17 at 1:33 pm
The suggestion from gebobs is a formula which will operate on a single cell (not on a range like c1.c11 as you tried above).

I notice however that you also talk about the possibility of a leading space with the leading hash ("# ") but gebobs formula doesn't deal with that. You can get rid of that space easily by surrounding the If formula with the Trim function (which will remove both the leading and trailing spaces)- I've added that in bold below. That means if you paste the text:

=TRIM(IF(LEFT(C1)="#",RIGHT(C1,LEN(C1)-1),C1))

into cell D1 (say) and press enter, you'll see D1 will display what's in C1 but less the leading # and/or space (if there is one) but leaving any other  #  or space still in the address.

If you then copy D1 into D2 to D11 (or fill down from D1), that column will display C1 to C11 but less any leading # (say in C8) or leading/trailing space.

Don't forget that column D only displays the results of the formula i.e if you change the corresponding cell in column C, it will change). You can operate on that (with other formulae) but at some stage I guess you'll want to fix the values in your address list (e.g. by copying and pasting as plain text from column D or your other output columns).

John_Ru (rep: 6092) Oct 19, '20 at 5:24 am
Add to Discussion

Answers

0

You can just do a find/replace for both scenarios.

Ctrl + F > Replace tab > in Find what type # or whatever you want to find and leave Replace with blank > hit Replace All

Do this for the # sign at the front and in the middle of the date and you should be good to go.

Discuss
0
How about...

=if(left(a1)="#",right(a1,len(a1)-1),a1) gebobs (rep: 20) Jun 8, '17 at 6:24 am
Discuss

Discussion

If that worked for you then go ahead and select it. It doesn't seem like gebobs is going to put it as an Answer. Credit him with the answer in the post but clean up the extra stuff after his username and put everything after the formula on a new line please (it's a bit difficult to read now)
don (rep: 1989) Jun 13, '17 at 12:03 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login