Rearrange data in cell

[MPullen]

I'm importing data from an external database.
It imports NAME as either a company name or personal name.
Company name shows as:
ABC Company LTD.
Which is great!

But when it imports a personal name, it's formated into one cell as:
PULLEN/MICHAEL

How can I automate it so I can rearrange the cell to show:
MICHAEL PULLEN

Any help is much appricated...

 

[Asala42]

G'day MPullen

I know there's a formula approach but a quick and dirty way would be to do a DataText to Columns - "/" as the delimiter.

That would break your example into 2 columns (or more if the range has a cell with more than 1 "/").

Basically you could then concatenate the order you want: something like =B2&" "&A2

Then paste special the column as values and kill the 2 extra columns. You could use in if statement to check for blank cells if it breaks into 3 or more columns (say due to an entry like "Smith/Joe/Mary")

Kinda manual I know, but quick and dirty.

Hope that helps,
Adam

 

[MPullen]

Thanks for you help.
I've got something to work with now.

But this spreadsheet is used everyday to pull lots of data.
I was hoping to automate it.

 

[Mark W.]

On 2002-04-24 11:18, MPullen wrote:
Thanks for you help.
I've got something to work with now.

But this spreadsheet is used everyday to pull lots of data.
I was hoping to automate it.

Why don't you change the query that produces the data set?

 

[Asala42]

Hey again,

Here's a formula approach that will work as long as you only have one "/" in the column. If your data is in column A:

In B2, =RIGHT(A2,LEN(A2)-LEN(LEFT(A2,FIND("/",A2)-1))-1)&" "&LEFT(A2,FIND("/",A2)-1)

'--I'm pretty sure this can be tweaked to be a bit shorter.

If you have some cells with more than one "/" then this complicates that formula a bit. I currently lack the caffeine levels in my bloodstream to play at that level. If you occasionally will end up with more than one "/"s then you may want to go with a VBA solution.

Hope that helps out somewhat,
Adam