Converting text based on criteria

[keith740]

good afternoon forum!

i have a list of employees (hundreds) split up into 3 teams in column A... they have tags at the end of their name, the tags are -AA,-AB, and - V

i need column B to display a converted version of the cell to left.

i need the names with -AA, and -AB in column A to show in column B as: move the tag to the front of name but only with one A,space,-,space,first name,last initial
example: John Wood-AB would show as A - John W


The names with V(column A) to show in column B as: move the tag to the front of name,space,-,space,first name,last initial,
example:John Wood- V would show as V - John W

Attached is an example and what i am looking for

I know this is an awkward request, is this possible?

Ty in advance

[FDibbins]

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?)

[AlKey]

Enter this formula in B1 and copy down

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

[FDibbins]

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

[oeldere]

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

[newdoverman]

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

Please Login or Register  to view this content.

[keith740]

Wow, blows my mind how you guys do this... great job everyone... they ALL work perfect!

with that said, i now have a sheet of formulas that are now showing up blank which were working fine when i was manually entering V and A in column B.

is there a way around this or would i have to manually enter?

[FDibbins]

What are those formulas?

[newdoverman]

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?

[keith740]

here are a few... putting together a dummy file now... will attach

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

[keith740]

10 minutes... ill have dummy file

[keith740]

hmm... now im really confused...

Works fine in the dummy file but in my original workbook all cells in "Team Data" sheet are empty...

[newdoverman]

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.

[newdoverman]

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

[FDibbins]

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?

[newdoverman]

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.

[newdoverman]

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

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[keith740]

figure it out, cant believe i missed that.

there is 1 cell in column A with "1" in it... this seperates employees that worked today from employees that didnt

1 error was throwing everything off lol

[FDibbins]

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

[keith740]

you guys are awesome... problem solved and works perfect now... ty sooo much for your help... sorry about the mess

[FDibbins]

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

[newdoverman]

Congratulations on figuring it out

[keith740]

Hello again Forum... Hope all is well!

I have ran into an issue and have been trying to figure out for hours now. (Wasnt sure if should start new post or work off this one since its pretty much same problem)

As many of you know i have 2 Teams... in "column B" Teams are distinguished by an "A" or "V"

My problem is we recently added a 3rd Team, which i would like to add to column B distinguished as "Z"

The new Criteria is either "- ZA" or "- ZB" (Both would read as Z in column B)

My old formula i used, which worked GREAT was:

Please Login or Register  to view this content.

Anyway i could tweak this formula to recognize new team?

Any help is greatly appreciated

Sheet 2 is Expected results

[newdoverman]

Try this.

Please Login or Register  to view this content.

[keith740]

works perfect... TY again Newdoverman!

[newdoverman]

You're welcome.