Hi,
I need a formula that will find the position of the first number in a string, and how many characters are in that number. For instance, it should return 2 and 6 for the value below.
T106300 7/14-7/15/16 GRT DEAN
I found this formula to get the position of the first number: =MIN(IF(ISNUMBER(FIND({0,1,2,3,4,5,6,7,8,9},A1)),FIND({0,1,2,3,4,5,6,7,8,9},A1)))
I need help with the second part.
Thanks for your help,
Craig
I am not very good at these formulas yet.
But this in B1 will give you the position of the first number in A1
Then all you need to do is find the first space or space after the first position.
Formula:
Please Login or Register to view this content.
This formula in C1 will give you the number of numbers.
Formula:Please Login or Register to view this content.
Last edited by mehmetcik; 04-04-2017 at 07:23 PM.
My General Rules if you want my help. Not aimed at any person in particular:
1. Please Make Requests not demands, none of us get paid here.
2. Check back on your post regularly. I will not return to a post after 4 days.
If it is not important to you then it definitely is not important to me.
You can use your first formula:
=SEARCH(" ",A1,MIN(IF(ISNUMBER(FIND({0,1,2,3,4,5,6,7,8,9},A1)),FIND({0,1,2,3,4,5,6,7,8,9},A1))))-MIN(IF(ISNUMBER(FIND({0,1,2,3,4,5,6,7,8,9},A1)),FIND({0,1,2,3,4,5,6,7,8,9},A1)))
or use result of the first formula (in this case: 2): =SEARCH(" ",A1,2)-2 or =SEARCH(" ",A1,B1)-B1 where B1=result of the first formula
Last edited by sandy666; 04-04-2017 at 06:58 PM.
With that sample...
T106300 7/14-7/15/16 GRT DEAN
what would your expected outcome be?
106300 7/14-7/15/16
or
106300
or something else?
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
Maybe something like this?
Formula:Please Login or Register to view this content.
Dave
Shorter version:
Position of the first number in the string:
Formula:Please Login or Register to view this content.
Length of the first numbers string:
Formula:Please Login or Register to view this content.
based on OP's example post #1
Last edited by sandy666; 04-04-2017 at 08:42 PM.
Another way to get first number.and to get first 6 numbers this array formula If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.Formula:Please Login or Register to view this content.Formula:Please Login or Register to view this content.
My sugesstion:
First position:
=MIN(IFERROR(SEARCH(ROW($1:$10)-1,A1),""))
Lenght:
=LEN(MAX(IFERROR(MID(A1,MIN(IFERROR(SEARCH(ROW($1:$10)-1,A1),"")),ROW(INDIRECT("1:"&LEN(A1))))+0,"")))
in which, first number is:
=MAX(IFERROR(MID(A1,MIN(IFERROR(SEARCH(ROW($1:$10)-1,A1),"")),ROW(INDIRECT("1:"&LEN(A1))))+0,""))
All are 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.
Quang PT
Post deleted
Last edited by craig04; 04-07-2017 at 04:11 PM.
Post deleted
Last edited by craig04; 04-07-2017 at 03:12 PM.
106300, or 2 & 6 so I can plug it into a mid formula.Originally Posted by FDibbins
Last edited by craig04; 04-07-2017 at 03:13 PM.
What if the first character after the number I'm searching for isn't a space?
Last edited by craig04; 04-07-2017 at 03:13 PM.
This was perfect.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks