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

1. ## 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. ## 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))))

3. ## 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.

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

5. ## 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. ## 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.``

7. ## 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.

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