how can i simplify this Formula?

I have a formula that looks to a cell and changes it. Part of the formula simply looks for "-" and takes off the spaces on either side and shrinks it so "47 - Left" becomes "47L". The big part of the formula changes names like "Cerebelum_4_5 - Right" to "Cer4,5R". Because the names are all different and the order in which I get the data changes every time I cant just assign a name to a helper column. I have to look where the name is in the column this time then change it.

The last part of the formula

Formula:

Please Login or Register  to view this content.

works great for all the number and letter names like "1 - Left"

for the ones with long names I have to write an IF statement for each name and there are 37 so I have 37 nested IF statements. It all works but I would like to see if there is a better way to do it.

Here is the formula

Formula:

Please Login or Register  to view this content.

Ill upload an example. In the example the formula is in B1 and dragged down to the end. The data is in D1 - Again the order of the data changes every time get new data.

a table with accordances and VLOOKUP

Where your comparisons are just a straight substitution, e.g.:

Cerebelum_Crus2 - Left … CerCru2L
Cerebelum_Crus2 - Right … CerCru2R
Cerebelum_Crus1 - Left … CerCru1L
Cerebelum_Crus1 - Right … CerCru1R
Cerebelum_3 - Left … Cer3L
Cerebelum_3 - Right … Cer3R
Cerebelum_4_5 - Left … Cer4,5L
Cerebelum_4_5 - Right … Cer4,5R
Cerebelum_6 - Right … Cer6R

and so on, you could just set up a small table by putting the longer strings in a column somewhere on the worksheet and the abbreviations in the adjacent column (suppose in columns X and Y), and then you could use this formula to change them accordingly:

=VLOOKUP(D1,X:Y,2,0)

so you won't need many of those multiple IFs. If you do that table down to:

Sub_Thalamus - Right … SubThaR

then I think your formula would become:

=IFERROR(VLOOKUP(D1,X:Y,2,0),IFERROR(,LEFT(D1,(FIND(" - ",D1,1)-1))&MID(D1,(FIND("-",D1)+2),1)),D1))

then you can copy it down.

Hope this helps.

Pete

I didn't spend time setting up the table to test it out fully !!

Pete

That works great thanks all

Tim, just curious about efficiency. Is vlookup more taxing one Excell than = ? I was going under the impression vlookup took more computing power so I was trying to look for a solution that allowed me to just use = in a cell. The program I'm using is bloated already and I would have to add about 2k vlookup formulas instead of 2k = formulas.

kevinu, two thngs:
table is sorted in alphabetical order
formula looks this way VLOOKUP(string, table, column number)
it works very fast