Hi,
I have the following which is in a column.
I need to extract the number in this column and delete the rest.
Please Login or Register to view this content.
I have tried text to column with fixed width but the number is in various parts of the column....
any ideas...
Last edited by pr4t3ek; 01-12-2009 at 01:51 AM.
--
Regards
PD
----- Don't Forget -----
1. Use code tags. Place "[code]" before the first line of code and "[/code"]" after the last line of code. Exclude quotation marks
2. Thank those who have helped you by Clicking the scales above each post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Press Alt-F11 to open the VBEditor
Click in Insert > Module
Paste in the following code:
Press Alt-Q to exit the VBEditor.Please Login or Register to view this content.
You've just added a new function to your sheet called LetterOut.
If a cell has your data in it, perhaps A2, then in B2, put this formula:
=LetterOut(A2)
Copy that down to get all the account numbers out of the strings.
Now highlight the entire range of cells you just created, and COPY, EDIT > PasteSpecial > Values. Now you have the account numbers and the formulas have been removed.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
hi, ive done exactly that but im getting a "#NAME?" error....
Post up the book, let's take a look....ooh, it rhymes.Originally Posted by pr4t3ek
ahh anyway for me to add it to the personal.xls instead of the book itself? because im trying to create a macro which does this...
also, please see attached the file, i dont want it to copy the 6 - **
i just want the long number ***
..
The UDF isn't going to work then, at least not without some "tweaking" I think might be unnecessary. It appears that the code is in the same place, at least as far as your sample goes.
In C4, enter this formula and copy it down:
=LEFT(RIGHT(B4,33),10)
oh yeh tats cool, that works... cheers mate!
nah hold uip lol
it aint working
e.g:
the last row is alrite.. but not the ones above..Please Login or Register to view this content.
sometimes it has the E5 or something before the - but sometiems it doesnt.. so thats my prob.. otherwise my text to column would work too
The formula gives the first 10 digits out of the LAST 33, so since last 33 digits all start with the numeric string, I can't see in your sample what the problem is. I'm using that formula on your posted book above and it's working, so something is different in yours.
Leave it as is not working, and post it again.
Hello PD,
Here is macro that uses pattern matching to extract the number. The function is set to look for a number that is at least 5 digits long and no more than 10 digits. I didn't know if these numbers digit count would change. You set the limits differently if you need to.
Sincerely,Please Login or Register to view this content.
Leith Ross
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks