Converting text based on criteria

Is all your data incosistant like that? Some have have space after the -, and some dont (I dont see any with a space before, but would that be possible too?)

Enter this formula in B1 and copy down

=TRIM(IF(OR(RIGHT(A1,2)={"AA","AB"}),"A - ","V - ")&LEFT(A1,FIND(" ",A1)+1))

OK, I guess it doesnt really matter, try this...
=MID(SUBSTITUTE(A1,"- ","-"),FIND("-",A1)+1,1)&" - "&LEFT(A1,FIND(" ",A1,1)+1)

a1 =if(RIGHT(A1,1)="V","V- ","A - ")&LEFT(A1,FIND(" ",A1,1)+1) and drag down.

Another variation on the theme: Enter in B1 and fill down.

Formula:

=IF(RIGHT(A1,1)="V","V - ","A - ")&MID(A1,1,FIND(" ",A1)+1)

What are those formulas?

Isn't column B where the formulae are? If you overwrite a formula, it is no longer there to do the calculation?

There is something here that hasn't been explained about column B.

Can you show us what is happening?

You have probably made a very small typographical error in your formulae. Another possibility is that the source names that were converted are not exactly the same in the real file as they are in the dummy. I suggest copying the formulae from the real file into a separate worksheet with a space before the = so that you can see the formula. Copy the same formulae from the dummy file and paste under the real workbook formulae and see what the differences are. A misplaced , or bracket will make all the difference in the world.

Seeing that all the cells are blank look at differences in the names because if the names are not correct, none of the formulae on the real worksheet will work as they are based on the names.

If you can't find the problem, upload your real file.

The dummy file works and the main 1 doesnt, but you sent the 1 that works

hard to say what the problem might be, without seeing what you have

are your row refs, correct?
does everything start inteh same row/columns?

I tend to use these versions of the ARRAY formulae because they are more tolerant of starting rows for the formula. I tested these in your dummy worksheet without problem.....who knows they might help but the originals should also work

Formula:

=IF(C2<0.01,"",IFERROR(INDEX(StatsforDay!A$2:A$300,SMALL(IF(LEFT(StatsforDay!$B$2:$B$300,1)="v",ROW(StatsforDay!$A$2:$A$300)-MIN(ROW(StatsforDay!$A$2:$A$300))+1),ROWS($1:1))),""))

Formula:

=IF(Q2<0.01,"",IFERROR(INDEX(StatsforDay!A$2:A$300,SMALL(IF(LEFT(StatsforDay!$B$2:$B$300,1)="a",ROW(StatsforDay!$A$2:$A$300)-MIN(ROW(StatsforDay!$A$2:$A$300))+1),ROWS($1:1))),""))

I just did a little test on the dummy worksheet. I messed up the names on the StatsforDay worksheet and if there is a problem with the names it would have shown up there immediately. The formulae in the real worksheet have to have a mistake in them.

These are array formulae, have you entered them properly.....easy to overlook.

the devil is in the details. Glad you got it resolved and thanks for sharing

Just happy you got it resolved, and thanks for the feedback

Congratulations on figuring it out

Try this.

Formula:

=LOOKUP(RIGHT(A1,2),{" V","AA","AB","ZA","ZB";"V","A","A","Z","Z"})&" - "&LEFT(A1,FIND(" ",A1)+1)

You're welcome.