+ Reply to Thread
Results 1 to 7 of 7

Extract first non-numerical characters from a named range and copy

  1. #1
    Registered User
    Join Date
    07-03-2021
    Location
    Scarborough, England
    MS-Off Ver
    365
    Posts
    4

    Extract first non-numerical characters from a named range and copy

    I'm seeking help for the last piece of the puzzle. I have a named range (a single column of data (H8:H300), and I'm looking to extract the first group of non-numerical characters from each cell within that range, and copy them into another named range of the same size in column A. I was previously using the following formula, but it is not updating as I need it to...

    =IFERROR(LEFT(BOM!H8,MIN(FIND({0,1,2,3,4,5,6,7,8,9},BOM!H8&"0123456789"))-1),"")

    Eventually I need to combine the results from column A, as a prefix to each result from another range...

    step 1:

    Col A Col H
    1 D D1 D2 D3
    2 C C5 C8 C20
    3 XE XE3 XE8
    4 D D4 D5 D6 D7
    5 CONN CONN1 CONN2


    Some of the sequences are hyphenated, and so the strings are manipulated in between this process (Someone from this Form very kindly solved that problem).


    Step 2:

    From this...

    Col A Col H Col I Col J Col K Col L
    1 D 1 2 3
    2 C 5 8 20
    3 XE 3 8
    4 D 4 5 6 7
    5 CONN 1 2


    to this...

    Col A Col H
    1 D D1 D2 D3
    2 C C5 C8 C20
    3 XE XE3 XE8
    4 D D4 D5 D6 D7
    5 CONN CONN1 CONN2

    I appreciate this looks exactly like the beginning example, but I haven't included the string manipulation that sits in the middle of this query to try and keep things simple.

  2. #2
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,455

    Re: Extract first non-numerical characters from a named range and copy

    IN K2 then copied across.

    =IF(COLUMNS($K2:K2)=1,$B2,IF(INDEX($B2:$I2,COLUMNS($K2:K2))="","",$B2&INDEX($B2:$I2,COLUMNS($K2:K2))))
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,537

    Re: Extract first non-numerical characters from a named range and copy

    Hi there.

    As you can see, the forum has messed up your formatting and you can not see what is where. REMOVE the confusion.

    A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

    Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet complete with an explanation and some expected results.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

  4. #4
    Registered User
    Join Date
    07-03-2021
    Location
    Scarborough, England
    MS-Off Ver
    365
    Posts
    4

    Re: Extract first non-numerical characters from a named range and copy

    Glenn, Thanks for the heads up. I'm still new at this (clearly )

    I've attached an XL document to explain what I'm trying to achieve
    Attached Files Attached Files

  5. #5
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,377

    Re: Extract first non-numerical characters from a named range and copy

    For the data provided.
    Please Login or Register  to view this content.

  6. #6
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,455

    Re: Extract first non-numerical characters from a named range and copy

    In A13
    Please Login or Register  to view this content.
    In H32. ARRAY formula for me. Since your version is 365 Array entry may not be required.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,537

    Re: Extract first non-numerical characters from a named range and copy

    I'd use this for Problem 1:

    =LEFT(H5,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},H5&"0123456789")-1))

    and the same formula as kvs for Problem 2.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 5
    Last Post: 05-23-2018, 09:35 PM
  2. Adjust existing macro to pick up specific named worksheet and copy to named range
    By Groovicles in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-08-2017, 12:03 PM
  3. Copy values of all named range in wb1 to identically named ranges in wb2
    By JAMIAM in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-24-2016, 06:58 PM
  4. Remove Text characters, leaving numerical characters
    By aschwalge in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 02-01-2016, 06:42 PM
  5. Need Help: Remove blanks from a named range containing numerical values
    By AColonyOfAnts in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-11-2015, 03:54 PM
  6. [SOLVED] VBA, remove all characters within a string, except numerical characters and full stops
    By rain4u in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-25-2015, 09:24 PM
  7. [SOLVED] Named range, maximum characters in RefersTo property
    By Jason Coley in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-02-2006, 05:55 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1