0

I have a sheet of text strings like the example:

06767996*Seg 010 Bligh St*Ernest Phillips Ave*Cooma*Adams Avenue Cooma*0.08

Where I want to extract the text between the last and second last astrix using excel formula. Any suggestions?

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
Stuart Cassin
  • 1
  • Do you always have the same number of asterisks in each string or will it vary? Also, have you considered using text to columns with `*` as a delimiter? – cybernetic.nomad Mar 04 '19 at 22:49
  • Use [Regex](https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops) - That'll do exactly what you need, and more – Selkie Mar 04 '19 at 22:59
  • If you need to do this in excel you will need to use a combination of `Mid`, `Find`, and [this (yikes)](https://stackoverflow.com/questions/18617349/excel-last-character-string-match-in-a-string) – urdearboy Mar 04 '19 at 23:01

1 Answers1

0

Try the following User Defined Function:

Public Function NextToLast(sin As String) As String
    arr = Split(sin, "*")
    NextToLast = arr(UBound(arr) - 1)
End Function

enter image description here

User Defined Functions (UDFs) are very easy to install and use:

  1. ALT-F11 brings up the VBE window
  2. ALT-I ALT-M opens a fresh module
  3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

  1. bring up the VBE window as above
  2. clear the code out
  3. close the VBE window

To use the UDF from Excel:

=myfunction(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and for specifics on UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Macros must be enabled for this to work!

Gary's Student
  • 95,722
  • 10
  • 59
  • 99