How to extract 4-digit long numeric value?
I want to extrcat 4 digit long numeric value from thousands of cells.
What it is: Alps, 3 to 999 worth,. 78999-../ zit,8877 99-../
What I want: 8877 (because it is 4 digit long number)
position of 4 digit numbers in the cells is not always same.
Some cells have no 4 digit number I want blank output for these cells.
please ask if you want to know something more abouth this problem, thanks.
Last edited by Atif Bhatti; 10-07-2021 at 10:54 AM. Reason: file attachment
Please see yellow banner on how to post a sample workbook; Ensure it is representative of your situation.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Thanks for the reply, I have tried to attach but failed because attachment menu is not working, tried refreshing the page too.
The paper clip icon does not work - we know that.
However, Manage Attachments as described in the instructions above does work. Try again, please (and read the instructions carefully first).
2108 is a release number - do you have Excel 2019 or MS365? Please update your profile accordingly.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
thanks, File attached and I am using MS365, version 2108.
The version is MS365 (2108 is just a release number). Please update your profile with the information we need. Thanks.
The attachment contains dates. It's NOTHING LIKE what you showed us in post #1.
Not what we were expecting .....
=convDte(A2)
Please Login or Register to view this content.
Last edited by JohnTopley; 10-07-2021 at 03:08 PM.
In fact I had encountered many time problems like this during my freelancing work so I was wanting to know the simple formula to extract different specific length characters. This year extraction sample file was just my recent encounter with this problem. It is not always year nor always 4 digit number. It may b different leghth multiple occurances in a signle cell. So I want a general formula for this problem. For example this formula (=MID(LEFT(A1,FIND(">",A1)-1),FIND("<",A1)+1,LEN(A1)) ) will extract anything between > and <.
Dear John please look into this VBA code as it worked perfectly for my porblem (A friend of mined wrote this for me)
Please Login or Register to view this content.
As I have found VBA to extract 4-digit Numeric value Now I wan two things from this forum,
First thing is "Now I want a VBA to extract 4-digit Alpha value"
Second thing is "I want VBA to to extract 4-digit Alpha+Numeric mix value"
Please modify this above given VBA for these two types of values. thanks again.
Last edited by alansidman; 10-07-2021 at 06:50 PM.
Here is a general solution that will handle digits, letters and alpha (a mix of digits and letters). It will return all matches (if more than one) as a comma/space delimited list.
The Enum will present the possible types of search automatically when you get to that argument in the function so make sure you include it when you copy/paste the above. The arguments for the function are the text to search, then the kind of search (Digits, Letters, Alpha) and finally how many characters you are searching for.Please Login or Register to view this content.
@Atif Bhatti
Code Tags Added
Your post does not comply with Rule 2 of our Forum RULES. Use code tags around code.
Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.
Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html
(I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)
Also, please update your XL version as it has been requested earlier.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
You are awesome Rick Rothstein !!!!!!!!!!!!!!!!!
Although your code not worked for but your all three patterns worked like a charm. Problem solved. Now I have all three types of data extraction VBAs.
Thank you excel forum. thank you so much.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks