Manipulating Text strings

Hi Guys,

Can you help me with my little problem.
I have a column filled with names per row. I wanted to standardize it by using the following format.
The comma after the last name is always an indicator to me that it is the last name.

<First Name> <Middle Initial(if any)> <Last Name>

My actual records contains the following:

BIMBO EPPING - OK
AHMAD SADICK - OK
Salana, Laura R - need to be arranged to > Laura R Salana
Cantos, Rhoda Grace J - need to be arranged to > Rhoda Grace J Cantos


thanks guys.

Hi,

with your names in column A

=IF(ISERROR(FIND(",",A1)),A1,MID(A1,FIND(",",A1)+2,99)&" "&LEFT(A1,FIND(",",A1)-1))

copy down

hth

I have a follow up question if I may.

How do I pass the formula to VBA code so as not to insert the formulas per cell.
Inserting the formula to each cell adds to the file size.

I have the following code initially created

Please Login or Register  to view this content.

Hope to hear from you guys soon... Im having sleepless nights over this report.

thanks.

xmoore, you've been around long enough now to know...

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

xmoore, given the above poster went to the trouble of putting together a detailed response I've modified your prior post...

at this point I noted you seemingly tried to add Tags but it went wrong...

To add CODE tags

[*CODE*]

your VBA

[/*CODE*]

without the asterisks of course...

David Huang - welcome to the board - going forward please refrain from posting on threads where OP has been requested to comply with Forum Rules but has yet to do so...

You could perhaps avoid Iteration altogether and use Evaluate...

Your ranges aren't very clear in truth

Please Login or Register  to view this content.

that said the above is not for the feint hearted and it would be just as easy to write the formulae en masse and overwrite with results thereafter - still avoiding iteration, eg:

Please Login or Register  to view this content.

adjust ranges to suit.

You don't specify which option you went for...

Using Evaluate:

Please Login or Register  to view this content.

Using Temp Formulae:

Please Login or Register  to view this content.

ie in both cases it's simply a case of encasing the True part of the initial IF within an UPPER function.

A general chit chat about it can be found at: http://www.ozgrid.com/forum/showthread.php?t=52372

It's most commonly seen to conduct SUMPRODUCT calcs in VBA

It's also a good way of conducting other Array formulae in VBA/memory given the fact it processes formulae as an Array by default (like say Conditional Formatting rules etc...) - eg:

Please Login or Register  to view this content.

Using it to avoid iteration etc is not a technique I dreamt up unfortunately rather it is something I picked up from Bill Jelen's book "Excel Gurus Gone Wild".