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