Extract text left and right of character

[stuartm4h]

We have cells with for example the following info...

A-10A SUPER DETAIL SETS - 1:48 Scale - 26001
'69 Camaro Z\28 2 in 1 - 1/25 - 85-2148

What were trying to do is to clean up the data and gain the scale info from each cell so for example

A-10A SUPER DETAIL SETS - 1:48 Scale - 26001 we would have a result of 1:48
'69 Camaro Z\28 2 in 1 - 1/25 - 85-2148 we would have a result of 1/25

The scale info could reside anywhere in the cell no set position and not always with the text scale in it the common elements I suppose will always be a 1: or a 1/ then obtaining the following set of no's till you hit a space would return the scale value.

[benishiryo]

hi stuartm4h, is it possible that it doesnt have any? if not, then:
=LEFT(REPLACE(A1,1,FIND(IF(ISERR(FIND(":",A1)),"/",":"),A1)-2,""),FIND(" ",REPLACE(A1,1,FIND(IF(ISERR(FIND(":",A1)),"/",":"),A1)-2,""))-1)

if it has, maybe:

Please Login or Register  to view this content.

[tigeravatar]

stuartm4h,

Welcome to the forum!
Here's an alternate formula:

Please Login or Register  to view this content.

[EDIT]
Managed to get it a bit shorter:

Please Login or Register  to view this content.

[stuartm4h]

Works a treat many thanks was side tracked with a little project then back to this one by the looks of things is going to be all about picking out keywords from strings so I can build up a defined breakdown of the info by key elements.