Extract text to left of certain character - limited to certain number of characters

[Country_Calc]

I need to extract the number to the left of the "/"

Data Example - B4: Innsbruck, University Hospital, (ads, peds), CIC 271, G. Gastl, D. Nachbaur (79 (82) 41/38)

Desired result = 41

The number could be 1 to 3 characters, but any method of getting closer would help.

I found an example on the board that gives me the number to the right of the "/" which works perfectly, but I can't figure out how to apply it to the left. This might not work because the number to the right always ends with a ")".


=TRIM(LEFT(SUBSTITUTE(MID(B4,FIND("/",SUBSTITUTE(B4,")","|",3))+1,
LEN(B4)),")",REPT(" ",LEN(B4))),LEN(B4)))

 

[steve the fish]

Is the space always there before the required number?

 

[Tetra201]

Try

=TRIM(RIGHT(SUBSTITUTE(LEFT(B4,FIND("/",B4)-1)," ","  "),3))

 

[Country_Calc]

Yes, there is always a space before the required number.

Tetra201 formula works pretty close to what I need =TRIM(RIGHT(SUBSTITUTE(LEFT(B4,FIND("/",B4)-1)," "," "),3))

Thanks Tetra201.

I can fix issues where single characters return a "(" before the data manually as there are not that many. A better formula would be appreciated though.

 

[steve the fish]

This should work for any length number.

=0+TRIM(MID(SUBSTITUTE(B4," ",REPT(" ",LEN(B4))),FIND("/",SUBSTITUTE(B4," ",REPT(" ",LEN(B4))))-LEN(B4),LEN(B4)))

 

[Country_Calc]

Thanks Steve - this is perfect, hope it helps someone else.

 

[Tetra201]

Please try the formula from Post #3 once again -- I forgot that the forum engine trims double spaces.

 

[steve the fish]

The one i suggested could be simplified a bit like this:

=0+MID(SUBSTITUTE(B4," ",REPT(" ",99)),FIND("/",SUBSTITUTE(B4," ",REPT(" ",99)))-99,99)

 

[Rick Rothstein]

Assuming the text being processed is never longer than 300 characters, this formula should also work...

=0+TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("/",A1)-1)," ",REPT(" ",300)),300))

 

[Gerald Higgins]

In post #4 you said

I can fix issues where single characters return a "(" before the data manually as there are not that many

If you actually meant

I can fix issues where single characters return a

) before the data manually as there are not that many

then I think this should work -

=MID(A1,FIND("/",A1,1)-3,3)+0

Or try this, which I think works for 1, 2, or 3 digit numbers

=(SUBSTITUTE(MID(A1,FIND("/",A1,1)-3,3),") ","0")+0

Basically it works for situations like this
123/ - 3 digits immediately before the / character
12/ - 1 space character and 2 digits immediately before the / character
) 1/ - 1 close brackets character, 1 space character, and 1 digit immediately before the / character.

Other combinations of characters may well fail.

Edit to add - sorry about the break in the second quote, I can't work out how to change that. It seems to be inserting quote and unquote tags where I don't want them, when I try deleting them it just puts them right back in !