Hi All,
I am looking for a formula that will help seperate alpha, numeric and other characters within a string.
Here are a few examples sw12-24, v9-17, SL7-19W
What I need to pull is just the numeric part of data 12-24, 9-17 and 7-19.
Any suggestions?
ldg
I would add a custom function to your workbook to do this with a formula that's sane to look at. A User Defined Function (UDF) to do what you want called CLEANALL can be used.
Here's the code:
=========
Please Login or Register to view this content.
How to install the User Defined Function:
1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save as a macro-enabled workbook
===========
How to Use the UDF
Place the strings in a column...let's use column A. The formula in B1 would be:
=CLEANALL(A1)
...just copy that down.
_________________
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!)
=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW(1:10),1)),0),COUNT(1*MID(A1,ROW(1:10),1)))
Important You must enter these formulas as arrays by pressing CTRL+SHIFT+ENTER.
for sw12-24 you will have to insert sw1224 the the array formula will give you 1224 then you can use a concatenate function to separate the month and date
i hope this is of sum help
Very nice.
A bit long but here it is
=LEFT(MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&1234567890)),25),MATCH(1,-MID(MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&1234567890)),25),{1,2,3,4,5,6,7,8,9,0},1)))
Row\Col sw12-24 12-24 v9-17 9-17 SL7-19W 7-19
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
HI Try this
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.Formula:
Please Login or Register to view this content.
☚ Click ★ just below left if it helps, Boo?ath?
Hello JBeaucaire..
Thank you for the help on splitting up alphanumeric cell content.
Is there anyway to alter the formula/code to split out numbers and characters from text?
For example - separating : R.S. ELITNIY 87% KAKAO BG 100G NU into 87%100
FIKSI SO SHOK.-OREKHOV.NACH.BM 45G VOE(N into .-..45(
Please help!
Thank you!
My function shows how to extract numbers OR text. You simply comment out one line and activate the other.
So if you need to do BOTH, create two versions of that function with different function names. Change CleanALL to NumOnly and in the second one TextOnly, as an idea.
Originally Posted by JBeaucaire
Hello again,
Thank you for the response. The thing is... I am trying to have numbers and symbol characters taken out... Ie leave all letters behind and strip out all the rest.
Is there a way to do that, please?
Thank you again.
Oh and... What does comment out mean? Sorry, not as clued up as most of the guys on here.
Thank you.
An apostrophe in VBA starts a "comment", so adding one to a line of code effectively turns it off while note deleting it.
Original with comments in green:
So you can create versions of this by changing the name of the function and then editing which line is commented and which is is not.Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks