+ Reply to Thread
Results 1 to 17 of 17

UDF to extract digits from variable range string

  1. 04-28-2014, 12:55 PM #1
    Excel2010101
    Excel2010101 is offline
    Registered User
    Join Date
    03-08-2014
    Location
    Los Angeles
    MS-Off Ver
    Excel 2010
    Posts
    22

    UDF to extract digits from variable range string

    I have a long string that has a list of digits in the middle that I want to extract. The string is variable length and the number of digits I want to extract is 5-7. I also have slashes in between the numbers I want to extract. I need a UDF that allows me to extract the 5-7 digit number from the string and restrict around the slashes (i.e. if two sets of digits in the string match the condition for extraction, extract the one around the slashes.) For example my original data is like

    1. aaa/12345/aaa/123
    2. aaaaa/123456/aaaaaa/3423
    3. 323/aa/1234567/aa

    and I want for results

    1. 12345
    2. 123456
    3. 1234567

    Is there a UDF that allows me to do this?

    thanks!
    Reply With QuoteRegister To Reply
  2. 04-28-2014, 01:02 PM #2
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: UDF to extract digits from variable range string

    Hi,

    Sorry - what does this mean:

    "if two sets of digits in the string match the condition for extraction, extract the one around the slashes"?

    Which of the strings you give is an example of this?

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/
    Reply With QuoteRegister To Reply
  3. 04-28-2014, 01:12 PM #3
    Excel2010101
    Excel2010101 is offline
    Registered User
    Join Date
    03-08-2014
    Location
    Los Angeles
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: UDF to extract digits from variable range string

    @ XOR LX

    Let's say I have aaa/12345/67890

    or

    aaa/12345/67890000

    I want to extract only 12345 in both cases and ignore the 67890 and 6789000
    Reply With QuoteRegister To Reply
  4. 04-28-2014, 01:18 PM #4
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: UDF to extract digits from variable range string

    Thanks. And by that I take it that you can't ever have, e.g.:

    aaa/12345/67890000/bbb

    Regards
    Reply With QuoteRegister To Reply
  5. 04-28-2014, 01:25 PM #5
    Excel2010101
    Excel2010101 is offline
    Registered User
    Join Date
    03-08-2014
    Location
    Los Angeles
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: UDF to extract digits from variable range string

    no. However, I will have data like aaa/12345/abc/67890

    I will have only one set of digits in between slashes but there will be other text characters in between slashes
    Reply With QuoteRegister To Reply
  6. 04-28-2014, 01:29 PM #6
    Joakim N
    Joakim N is offline
    Forum Contributor
    Join Date
    04-24-2012
    Location
    Karlstad, Sweden
    MS-Off Ver
    Excel 2016
    Posts
    232

    Re: UDF to extract digits from variable range string

    I don't know if this is what you want. Maybe I don't understand 100% what you want.
    Attached Files Attached Files
    1. Reply to thread and inform if suggestion was helpful or not
    2. Click on the star (=Add Reputation) if you think someone helped you
    3. Mark [SOLVED] to this thread if solution was found. (On Menu "Thread Tools" > "Mark this thread as solved")
    Reply With QuoteRegister To Reply
  7. 04-28-2014, 01:32 PM #7
    Joakim N
    Joakim N is offline
    Forum Contributor
    Join Date
    04-24-2012
    Location
    Karlstad, Sweden
    MS-Off Ver
    Excel 2016
    Posts
    232

    Re: UDF to extract digits from variable range string

    Sorry, I was reading your request again. I misunderstood. I thought it was position 5 or 7 in the string you wanted to extract. Now I understand it is 5-7 of numbers long. I will write that code. Maybe someone will be faster.
    Reply With QuoteRegister To Reply
  8. 04-28-2014, 01:33 PM #8
    Excel2010101
    Excel2010101 is offline
    Registered User
    Join Date
    03-08-2014
    Location
    Los Angeles
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: UDF to extract digits from variable range string

    @joakim N Thank you! However, could you write your code in forum? I cant download files.
    Reply With QuoteRegister To Reply
  9. 04-28-2014, 01:34 PM #9
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: UDF to extract digits from variable range string

    Perhaps:

    =LOOKUP(1E+307,0+TRIM(MID(SUBSTITUTE(A1,"/",REPT(" ",999)),999*(ROW(INDIRECT("2:"&LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))-1)+1,999)))

    Regards
    Reply With QuoteRegister To Reply
  10. 04-28-2014, 01:38 PM #10
    Joakim N
    Joakim N is offline
    Forum Contributor
    Join Date
    04-24-2012
    Location
    Karlstad, Sweden
    MS-Off Ver
    Excel 2016
    Posts
    232

    Re: UDF to extract digits from variable range string

    Now the returned number is 5-7 digits in length.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  11. 04-28-2014, 01:40 PM #11
    Joakim N
    Joakim N is offline
    Forum Contributor
    Join Date
    04-24-2012
    Location
    Karlstad, Sweden
    MS-Off Ver
    Excel 2016
    Posts
    232

    Re: UDF to extract digits from variable range string

    Here is the function:

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  12. 04-28-2014, 01:45 PM #12
    Joakim N
    Joakim N is offline
    Forum Contributor
    Join Date
    04-24-2012
    Location
    Karlstad, Sweden
    MS-Off Ver
    Excel 2016
    Posts
    232

    Re: UDF to extract digits from variable range string

    Fantastic how you wrote that formula:

    Quote Originally Posted by XOR LX View Post
    Perhaps:

    =LOOKUP(1E+307,0+TRIM(MID(SUBSTITUTE(A1,"/",REPT(" ",999)),999*(ROW(INDIRECT("2:"&LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))-1)+1,999)))

    Regards
    Reply With QuoteRegister To Reply
  13. 04-28-2014, 01:46 PM #13
    Excel2010101
    Excel2010101 is offline
    Registered User
    Join Date
    03-08-2014
    Location
    Los Angeles
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: UDF to extract digits from variable range string

    @ XOR LX: It works.

    @ Joakim N: Thanks for trying but it gives me the #Name error.
    Reply With QuoteRegister To Reply
  14. 04-28-2014, 01:46 PM #14
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: UDF to extract digits from variable range string

    @Joakim Thanks! Though I'm sure your UDF is a lot more efficient!

    @Excel2010101 You're welcome!
    Reply With QuoteRegister To Reply
  15. 04-28-2014, 03:10 PM #15
    tom.hogan
    tom.hogan is offline
    Forum Contributor
    Join Date
    01-25-2011
    Location
    Virginia
    MS-Off Ver
    Excel 2016
    Posts
    166

    Re: UDF to extract digits from variable range string

    This will work too assuming there is only one number between two slashes, it does not work if there is the possibility of an invalid number (1-4 or greater than 7 digits) between two slashes.

    Please Login or Register  to view this content.
    Regards,

    Tom
    Last edited by tom.hogan; 04-28-2014 at 03:16 PM.
    Reply With QuoteRegister To Reply
  16. 04-28-2014, 03:35 PM #16
    GC Excel
    GC Excel is offline
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,412

    Re: UDF to extract digits from variable range string

    Hi,
    Here's a suggestion using Regular Expression : 

    Please Login or Register  to view this content.
    GC Excel

    If this post helps, then click the star icon (*) in the bottom left-hand corner of my post to Add reputation.
    Reply With QuoteRegister To Reply
  17. 04-28-2014, 03:41 PM #17
    GC Excel
    GC Excel is offline
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,412

    Re: UDF to extract digits from variable range string

    And this version if you want to accept only 5 to 7 characters long : 

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel Formula to Extract Numbers (atleast the 1st 8 digits) from an Alpha-Numeric String
    By Vinod Krishna.C in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 03-08-2014, 03:25 PM
  2. [SOLVED] How to extract digits from a string?
    By GIS2013 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-17-2013, 09:28 AM
  3. [SOLVED] Excel 2007 : Extract last four digits from alphanumberical string
    By questionguy in forum Excel General
    Replies: 5
    Last Post: 07-16-2012, 12:51 PM
  4. How to extract one or two 11 digits numbers from an alpha numeric string
    By neo4u44 in forum Excel General
    Replies: 11
    Last Post: 11-16-2011, 12:56 PM
  5. [SOLVED] Formula to extract digits from a text string?
    By [email protected] in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-15-2006, 12:16 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1