Hi Guys
if I have a column with some sell have the street number front of the street name and others have only the street name and I want to get rid of all the numbers on front of the street name without individually go to every cell and delete it.
is there a formula help me do that. sheet attached
thanks
If your numbers (if they exist) are always at the beginning of the address, then you just need to see if the first character is a number - if it is then remove everything before (and including) the first space, otherwise leave it alone. This is what this formula does in B1:
=IF(ISNUMBER(--LEFT(A1)),RIGHT(A1,LEN(A1)-FIND(" ",A1)),A1)
Copy it down as far as you need to.
Hope this helps.
Pete
Or,
=TRIM(SUBSTITUTE(A1,LEFT(A1,SUMPRODUCT((LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},""))))),""))
Jesus
it works
pete_uk please explain it to me in more details
Berlan
not very used on sumproduct function.. to high for me. thanks
I don't know what more detail I can give you that isn't in my explanation.
LEFT(A1) looks at the first character in cell A1. By surrounding this with ISNUMBER(-- and ), this tests to see if that first character is a number. If it is then you want to take the RIGHT hand part of A1 - the number of characters you want to take is given by the LENgth of the string minus the position where you can FIND the first space. If the first character of A1 is not a number then just take A1 as it is.
Hope this helps.
If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.
Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).
@Berlan - thanks for the rep.
Pete
Using Pet's formula...
=IF(ISNUMBER(--LEFT(A1)),Find(" ",A1)-1),A1)
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Welcome to the forum.
In addition to Pete's formula (a good one by the way) I found that this also works:
Formula:
Please Login or Register to view this content.
Explanation:
If the leftmost character is a number which is determined by ISNUMBER(--LEFT(A1)) LEFT(A1) will extract the first character of the string but regardless of being a number or a letter, it will be text. --LEFT(A1) will force the character if a number to be a number. The ISNUMBER(--LEFT(A1) is used to create a TRUE or FALSE for the IF. If the first character is a number then this will be TRUE and the TRUE part of the formula is returned.
TRUE PART OF THE IF FORMULA:
MID(A1,FIND(" ",A1)+1,30)
The MID function returns values from the middle of a string of characters. The first argument is WHAT STRING, followed by where to start, and finally how many characters.
FALSE PART OF THE IF FORMULA:
This is simply the original cell (A1) in this case.
I hope that is has been understandable.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Hi Ron,
30 might not be enough characters - I would suggest 255, so really you are saying "to the end of the string".
Pete
Thank you guys Alllll
I just used 30 it could well have been 3000 as MID will truncate it. 30 was enough for the demo.
I probably should have used more than 30.
Thanks Pete.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks