Extract of last 4 digits or insert alternative number of a cell

**Mansfieldexcel** · 05-23-2019, 06:00 AM

I have some static data which I now wish to extract, via a formula, the last 4 numerical values of the text . The last 4 digits will be 1 to 4 chars long. However, if there are no numbers in the last 4 digits of the text then insert an alternative number (see example data). I have been using the =right() formula without much success.

I have included a sample of sanitised static data and my preferred results.

I would appreciate if you can assist with a resolution to my challenge. As always thank you for your consideration and time.

**XLent** · 05-23-2019, 06:49 AM

Not entirely sure I follow order of precedence as your expected results contradict (i.e. all "Text" examples {rows 17+} would be 99990 given rule 1)

So, the below is setup to ape your expected results (rather than follow rule ordering in example)

Results would mirror sample file bar those in first 16 rows for which there is no Text* string, nor number -- so presumed 99990 rather than RIGHT(cell,4) as you have displayed.

Formula:

Please Login or Register  to view this content.

note: I've assumed, per rules, that any instance of text*(substitute day) gets 99981 as result -- if this isn't the case the above would need to be modified.

**Mansfieldexcel** · 05-23-2019, 08:57 AM

Thank you for responding so quickly. Much appreciated.

The resolution you very kindly provided works on my sanitised example data. I Thank you!

You are correct any instance of text*(substitute day) gets 99981.

However, when I copy the formula to my real data I do have some issues which I trust you can assist with (my live data starts in column H2 and ends around column H200). I did change all instances in the formula of A1 to H2

In the example data I provided "text 1" to "text 13" is actually variable text. I should have made that clearer. Apologies I was keen to sanitise the static data.

So for example, if I change text1 to abc then it now gets the code of 99990 which would be incorrect. Therefore, is it possible to treat "text1" to "text13" as variable text?

e.g. if I change text1 to variable text say "abc" then the code it gets should be 99950 and text2 to variable text "xyz" then the code it gets should be 99980 and so on to text 13 to variable text "def" it gets code 99980.

I have attached an updated desired results worksheet.

Thank you again for your time and patience.

**XLent** · 05-23-2019, 09:46 AM

So, I'm not sure how to provide anything re: latest sample as the variable texts aren't provided, or at least I couldn't see them.

In principle however, yes you can modify re: variable text, replacing below:

LOOKUP(9.99E+307,SEARCH("text"&ROW($1:$15)&"(",SUBSTITUTE($A1," ","")),{#1,#2, etc...})

with:

LOOKUP(9.99E+307,SEARCH({"abc","...",....,"xyz"},$A1),{#1,#2, etc...})

i.e. each variable string within an inline array -- then modify the 2nd inline array (numbers) such that they align to the variable text value (i.e. abc get first value in 2nd inline array)
note: I had 15 #s in my sample as there were results for Text 1 through Text 15

you can, if you prefer, replace the inline arrays {...} with a reference to a range that holds the relevant values.

note: you may need to handle possibility of strings that are not mutually exclusive - e.g. if car and carpet were both search terms then you need to handle that (via imposed delimiters)

**Mansfieldexcel** · 05-23-2019, 10:18 AM

Unfortunately what you suggest in modifying the formula is beyond me.

The variable texts are in the attached spreadsheet v0.4. On sheet "required result 3" if it assists?

Thanks again for your time

**XLent** · 05-23-2019, 10:29 AM

well, if Required result 3 is the lookup list (with value) then:

Formula:

Please Login or Register  to view this content.

as you noted previously, if you're real file starts in H2 then modify A1 references to H2.

HTH

**Mansfieldexcel** · 05-23-2019, 10:49 AM

Thank you for your assistance and patience. I think I will have to rethink what I am trying to do. The solution you have very kindly provided works all ok with the numbers but with the variable text that requires a unique number (depending on the text) it just defaults to 99990.

**XLent** · 05-23-2019, 11:08 AM

in your sample file, yes, assuming you're applying the formula to Example Data -- that's because none of the strings in Required Result 3 (col A) appear in your Example Dataset (col A).

To elaborate by means of example:

Example Data!A95: TSaU-text 9 (C-AM) --> expected result denoted as 99960, I think...?

The only item that maps to 99960 on your Required Results table is TSaU-Maint*

so assuming you were to swap out text 9 for Maint or Maintenance etc on your Example data it would work and return 99960... as-is it will return 99990 as there is no match

If the above is off the mark I'm afraid you'll need to start again with an example that may be easier for folk to follow
of course in the interim someone else might interpret the request correctly...

**Mansfieldexcel** · 05-23-2019, 11:58 AM

OK. I have had a rethink of my approach which hopefully will clarify matters - see attached document.

Many thanks for your continued patience

**XLent** · 05-23-2019, 12:15 PM

sorry, I'm a little lost -- I'll ping a few others and ask for some fresh sets of eyes....

**Mansfieldexcel** · 05-23-2019, 12:20 PM

Ok thank you. is there anything further I can provide to further clarify and which will assist you?

**Bo_Ry** · 05-23-2019, 02:07 PM

Please try at C1
=IF(A1="","",IFERROR(IFERROR(-LOOKUP(,-RIGHT(A1,{1,2,3,4})),LOOKUP(,-SEARCH(A$17:A$22,A1),B$17:B$22)),99980+COUNTIF(A1,"*team*")*10+COUNTIF(A1,"*substitute*")))

**Mansfieldexcel** · 05-24-2019, 04:58 AM

Thank you for your assistance. Your solution appears to be working for the extract of the numbers and extracting the text *substitute*"

However, for the variable text "*team*". The text "team" may not be in my static data. In fact it could be any text. The number that should be allocated to this category is: 99990

In addition, whist I have only 80 lines in my example static data in fact it could be around 400 lines. The formula you have very kindly provided can this be expanded? say from A1 to A400? I have tried to change the formula to reflect but I get "0" if I go beyond A80.

Thank you again for your time and patience.

**bebo021999** · 05-24-2019, 06:25 AM

You have 2 options: Using a reference table or not using.
Reference table is in I6:J12

Option 1:

Please Login or Register  to view this content.

Option 2:

Please Login or Register  to view this content.

Both are array formulas, must be confirmed with Ctrl-shift-enter, not enter only.

My Ideas are:
1) 1st priority: extract last 1-4 numbers: MAX(RIGHT(A4,{1,2,3,4})+0), if not
2) 2nd priority: Searching list is in I column in reference table, then return value in J column, (plus 1 if there is "substitute" in text)
3) Lastly, "99920"

Hope it works.

**Mansfieldexcel** · 05-24-2019, 07:42 AM

Thank you for your solution.

My preferred solution at present is option 2 which in my initial testing appears to be working great. Brilliant!

Can the formula for option 2 be modified for cells that are blank. ie. if a cell in column A is blank return a blank? at present it returns 99920

I will continue my testing on my 'live' data.

Thanks again

**bebo021999** · 05-27-2019, 05:44 AM

Originally Posted by Mansfieldexcel

Can the formula for option 2 be modified for cells that are blank. ie. if a cell in column A is blank return a blank? at present it returns 99920

Try something like this:
=IF(A1="","","your main formular")

**Mansfieldexcel** · 06-04-2019, 05:42 AM

My issue has been successfully implemented. I wish to thank all those who reviewed and responded to my questions. It is very much appreciated. A great job by all!

**Mansfieldexcel** · 06-04-2019, 05:45 AM

My challenge has been successfully implemented. Thank you for all those who contributed. It is very much appreciated.

Extract of last 4 digits or insert alternative number of a cell

LinkBack

Thread Tools

Rate This Thread

Display

Extract of last 4 digits or insert alternative number of a cell

Re: Extract of last 4 digits or insert alternative number of a cell

Re: Extract of last 4 digits or insert alternative number of a cell

Re: Extract of last 4 digits or insert alternative number of a cell

Re: Extract of last 4 digits or insert alternative number of a cell

Re: Extract of last 4 digits or insert alternative number of a cell

Re: Extract of last 4 digits or insert alternative number of a cell

Re: Extract of last 4 digits or insert alternative number of a cell

Re: Extract of last 4 digits or insert alternative number of a cell

Re: Extract of last 4 digits or insert alternative number of a cell

Re: Extract of last 4 digits or insert alternative number of a cell

Re: Extract of last 4 digits or insert alternative number of a cell

Re: Extract of last 4 digits or insert alternative number of a cell

Re: Extract of last 4 digits or insert alternative number of a cell

Re: Extract of last 4 digits or insert alternative number of a cell

Re: Extract of last 4 digits or insert alternative number of a cell

Re: Extract of last 4 digits or insert alternative number of a cell

Re: Extract of last 4 digits or insert alternative number of a cell

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Extract 2 digits from a 3 digit number in different sequences

Extract Numbers from Cell if Number is 3 or 4 digits in access programming

Extract Numbers from Cell if Number is 3 or 4 digits in access programming

[SOLVED] Extract unique digits within number

5 digits number extract

extract digits from a cell

Sum digits in a cell plus the number digits-Ready for this

Bookmarks

Bookmarks

Posting Permissions