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: 1482) 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
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: 1482) 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