Formula to search and extract text with variable character lengths

[NMitch]

G'day y'all, I have a column of text with variable text. The text will have Lot and DP numbers, but the Lot and DP string is of variable length; however, they all follow a set format.

Lot numbers start with "Lot", then a space " ", then a number - which can be up to four digits long, but always a number, which is then followed by a space " ".

DP numbers all start with "DP", no space, then a number (eg. DP3443). The DP numbers are usually at the end of the text in the cell, but not always; however, there are no spaces within the DP number.

I would like to extract the Lot and DP numbers, without the letters, into a string which will have the format ##.$$$$, where the ## is the Lot number and the $ the DP number (ie. all I want are the integers).

While it is obvious there will be various formulae such as Search(), right(), etc, I don't know enough to know how to search the forum for exactly what I need.

Thank you in advance,

Mitch

 

[Trebor76]

Hi Mitch,

I didn't write this but it should do the job for you:

=REPLACE(A2,1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1,"")+0

Just change the cell reference from A2 to suit.

Regards,

Robert

 

[NMitch]

Thanks Robert,

However, I neglected to say there are other numbers in the string of text in the cell. I replaced both instances of A2 with B3, which is the first cell I want to extract the Lot and DP numbers from, but it returned #VALUE. Interestingly, when I open the formula Function Arguments screen, it appears to correctly identify the Lot number, just not the DP number.

Thanks,

Mitch

 

[Trebor76]

What is in cell B3?

 

[NMitch]

Hi Robert, cell C3 has text in the format of DESCRIPTION LOCATION LOT XXX DPXXXX

Description is variable length with letters and "&".
Location is an address (without state and zip/post code)
Lot is followed by up to four characters
DP is a string which includes DP, then a number, with no spaces in between

What I probably need is a formula to find the word "Lot", return the number between the first space after Lot and the space after the number, then ".", then find "DP" after the Lot number, and return the number attached to DP - but there may be spaces after the DP number, with more text (such as "Section E").

For example:
House 21 Prince Street Cobar Lot 1 DP3809 Section E

I would like a formula to return 1.3809, which will be a unique identifier as every parcel of land has a separate Lot and DP number.

Regards,

Neil

 

[Trebor76]

I'm sure there's an easier way but this will do the job:

=LEFT(MID(C3,SEARCH("LOT",C3)+4,255),SEARCH(" ",MID(C3,SEARCH("LOT",C3)+5,255)))&"."&LEFT(MID(C3,SEARCH("DP",C3)+2,255),SEARCH(" ",MID(C3,SEARCH("DP",C3)+3,255)))

If you need it as an integer use the VALUE function like so:

=VALUE(LEFT(MID(C3,SEARCH("LOT",C3)+4,255),SEARCH(" ",MID(C3,SEARCH("LOT",C3)+5,255)))&"."&LEFT(MID(C3,SEARCH("DP",C3)+2,255),SEARCH(" ",MID(C3,SEARCH("DP",C3)+3,255))))

Robert