Excel 2007 : Is it possible to reverse data in a cell

Hello All,

I'm working on a huge spreadsheet that contains a lot of data and a couple worksheets. There is one section of a worksheet that has multiple cells with people’s names in them. The cells are formatted as 'last name, first name'. On the next work sheet there are all the same entries for names. However on this sheet the cells with names are formatted as ‘First name, Last name’, which is a reverse order from the previous work sheet. I want to do a Vertical Look up to pull data in from one work sheet to the next. The common denominator or 'lookup_value' would be the name. However the vertical look up doesn't work, because the names don't match, since they are formatted differently on each work sheet. So my question is, is there a formula or way to reverse the data in a cell. I would like to take the data that is formatted as First Name, Last Name and do a formula that would flip flop based on the 'comma', so that all these cells would now be Last Name, First name. Any help anyone can provide would be greatly appreciated.

Thanks in advance.

PS. This was my first post, but I anticipate posting more, as this is a very useful site. So if anything was wrong w/ my post, please let me know as I'd like to fully adhere to the rules and regulations of the board.

Thanks again all!

Hi,

Try this: =TRIM(RIGHT(A1,LEN(A1)-FIND(",",A1))&", "&LEFT(A1,FIND(",",A1)-1))

Dom

I am trying to do the exact same thing (although first and last names are separated with a space); how do I apply the formula to the column with names?

EDIT: I should clarify my question - is there a way to reverse the list of names in the existing cells, instead of creating a second list that uses the first as a reference?

Thanks!

Accountant, welcome to the forum

1st, apart from manual or VBA, there is no way to have contents of a cell adjusted.

2nd Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.