Extract Digits with relevant characters

[ElmerS]

Hi,

I have a list (col. Q) of items incl. other parameters - such as:

18 Kg Cement
7/32" Screw
Wooden board board 4.5"/5.5"
12.34 meters wire

In general I'm looking for a Sheet Formula (no UDF - which I already wrote) in order to extract the Numeric Digits(0,1,2,3,4,5,6,7,8,9) AND all relevant characters.

Requested returns:
18
7/32"
45"/5.5"
12.34

In fact I would like to extract CHAR(35) up to CHAR(64)

What will be the formula to achieve that ?

Thanks, Elm

[rwgrietveld]

but inch is CHAR(34) ?

Why inches and not meters?

[ElmerS]

Sorry for the TYPO

Results should read:

18
7/32
4.5/5.5
12.34

Thanks, Elm

[martindwilson]

use your udf! if it works.
seems a waste of effort trying to parse those examples.
is that the only way things are presented?

[ElmerS]

Thanks.

All digits can be mixed with/among CHAR(34) ---> CHAR(47) and CHAR(58) ---> CHAR(64)

No guarantee how many repetitions there will be within one string.

It could be also: Block 4.5/5.5/6.5 (requested result: 4.5/5.5/6.5)

As I have the UDF that handles it - I still would like to learn if and how this can be achieved with a "pure" Worksheet formula.

If you have something in mind and don't care to spend a few moments - I will appreciate.

Elm

[rwgrietveld]

Fully agree with Martin, UDF is most readable

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[martindwilson]

how about a few helper columns

[ElmerS]

Fully agree with Martin, UDF is most readable

Please Login or Register  to view this content.

Please Login or Register  to view this content.

As I don't like codes to be "too long" - I will stick to my UDF as long as I won't get the requested formula.

Please Login or Register  to view this content.

Elm

[ElmerS]

Thanks, Martin,

With all due respect, especially not knowing the serious level (from 1-10) of your suggestion, therefor with you permission I will not comment about your "Helper-Columns" suggestion.

Elm

[martindwilson]

well it just demonstrates the levels of nesting you'd need,thats why a function isn't probably the best idea

[rwgrietveld]

With all due respect, especially not knowing the serious level (from 1-10) of your suggestion, therefor with you permission I will not comment about your "Helper-Columns" suggestion

As I don't like codes to be "too long" - I will stick to my UDF as long as I won't get the requested formula.

Sounds like you are dissapointed, but I think Martin just showed you that it will be a "unreadable" formula. The UDF you wrote is straight forward and easy to read. Much better. I would go for that.

[ElmerS]

This is as closest as I could get with a "2007" ws formula.

It could probably be more sophisticated.

Elm

[martindwilson]

fair point
but it falls down if text after number string
say
Wooden board 4.5/5.5 black
=SUBSTITUTE(MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1234567890)),256),CHAR(34),"")
returns
4.5/5.5 black
so you'd have to nest it again possibly several times

[ElmerS]

Probably, but I assume the nested levels won't be as many as Martins amount of columns.
OR:
I will force the user to type: Black Wooden board 4.5/5.5

Thanks, Elm

[rwgrietveld]

Elm,

can't let it go. This is the best I can do

where MyRng is a named range:

Hope you like it (I like your UDF better)

As you are not a NOOB, you've probably seen its a CSE function (Array function)

[ElmerS]

Thank you very much.

I am glad you took it as a challenge and the result looks promising.

Elm

[ElmerS]

Hi, Ricardo,
5 more minutes of your precious time, to look at the attached WB, please.

I have decided to reduce the alphabetic characters to only '/' and '.' - so, I have declared an additional named range: MyRng2 and changed your formula to meet the new reduced request.

Cells B10:B12 return the expected values however, cells B13:B16 does not.

What did I do wrong ?

btw: I expected your formula to return what I hand-typed in C7 and not what it returns in B7.

Thanks again for all your efforts,
Elm

[rwgrietveld]

It's simple.

You have adjusted the range from A1:A30 to A1:A14.

The 30 is arbitrary (can be 100) but it should at least be longer the the max # of characters in the string. 14 does not comply.

Solution.

Extend range 14 to > 30 for both the formula as well as the named range.

[ElmerS]

Wooowww, we are almost there.

It works as expected on A10:A15 - however, for A16 it returns: 4.5*5.5*6.5 although the character '*' (Code 42) is not within: (MyRng2>45)*(MyRng2<58)

For A16 I expect: 4.55.56.5

Hope you can help me with that, probably, last issue.

Thanks, Elm

[rwgrietveld]

My formula looks at the first and the last numerical and takes ALL numericals in between.

55 ALL 55 will not change either
only 55 ALL and ALL 55

... and this threa is closed for a while. Open a new one. so others will be invited to contribute (or UN-SOLVE this thread)