Formula to Extract Numbers from String

[JamesA11]

Hi,

I have a cell that contains strings such as the example below:

['12-3', '11-6', '3-1']

Each of the values in apostrophes is a reference. Sometimes the will be a single reference (eg ['111-2']), sometimes two and sometimes three. Each reference is always encased within apostrophes.

Is it possible to extract the references with formulae to give the following result (using the above example)? ie each reference in a separate cell and the apostrophes, etc removed.

12-3
11-6
3-1

Thanks

 

[Joe4]

Let's say that your entry is in cell A1.

Then here are three formula that will work:

First entry:

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"[",""),"]",""),"'",""),",",REPT(" ",100)),1,100))

Second entry:

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"[",""),"]",""),"'",""),",",REPT(" ",100)),101,100))

Third entry:

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"[",""),"]",""),"'",""),",",REPT(" ",100)),201,100))

 

[JamesA11]

Wow Joe, that's incredible! No wonder I couldn't do it. Thank you very much.

 

[Joe4]

Wow Joe, that's incredible! No wonder I couldn't do it. Thank you very much.

You are welcome.
That is just one way. There are other (maybe shorter) ways.

Though the formula is long, it really isn't too complicated. We are using three of those "Substitute" functions to remove the unwanted characters of [, ], and '.
And then we are replacing the commas with 100 spaces.
Then we are just taking the first 100 characters for the first one, and trimming off all the extra spaces.
Then taking the next 100 characters for the second one, and trimming off all the extra spaces.
etc.