Fix names with code

**Xrull** · 02-16-2010, 11:19 PM

Good day,
I have a spread sheet where I've got to change the names, eg The name Paul Dechristian with this formula:

Please Login or Register  to view this content.

would be Dechristian, Paul.
I tried using a code Donkeyote used to solved a problem on this forum:

Please Login or Register  to view this content.

I changed it to this:

Please Login or Register  to view this content.

but I get #VALUE! for all the cells.
What am I doing wrong?
Thanks,
Xrull

**mdbct** · 02-16-2010, 11:43 PM

Here is the Exapmle1 from below.

Please Login or Register  to view this content.

this part

Please Login or Register  to view this content.

should be

Please Login or Register  to view this content.

**Xrull** · 02-17-2010, 12:08 AM

mdbct,
The code cuts off the first name, it doesn't change Paul Dechristian to Dechristian, Paul.
Thanks,
Xrull

**mdbct** · 02-17-2010, 12:32 AM

That's what the formula in the Example1 that you posted was set up to do. I didn't change the base formula in Example1.

**mdbct** · 02-17-2010, 12:45 AM

Try this one

Please Login or Register  to view this content.

I tried using the same method

Please Login or Register  to view this content.

This method didn't work. For some reason all cells were duplicates of cell B2.

**Xrull** · 02-17-2010, 12:53 AM

mdbct,
The code worked OK,
Thanks,
Xrull

**DonkeyOte** · 02-17-2010, 02:37 PM

FWIW, using formula based approach of:

Please Login or Register  to view this content.

applied to Range simultaneously via Evaluate

Please Login or Register  to view this content.

**DonkeyOte** · 02-17-2010, 02:48 PM

I missed this bit...

Originally Posted by mdbct

I tried using the same method

Please Login or Register  to view this content.

This method didn't work. For some reason all cells were duplicates of cell B2.

The initial ISTEXT test in the earlier example(s) actually serves a dual purpose

1 - as implied it allows us to differentiate between Text and Non-Text entries such that we can apply an appropriate function to underlying value

2 - it crucially creates an array of Boolean outputs - one per cell referenced in the range.
It is this array of Booleans that permits us to essentially run the Evaluate over the range on a cell by cell basis - conducting an IF on each cell and acting accordingly (True,False).

Without the ISTEXT the Evaluate will be conducted against the first cell in the range and result applied to entire range.

More commonly you see

Please Login or Register  to view this content.

ie ROW is used to generate numeric values as the IF test (only 0 is FALSE of course) ... however here it makes sense to use an actual ISTEXT test given we want to process differently
(ie we don't want to apply our formula to numeric cells etc)

**mdbct** · 02-17-2010, 04:31 PM

Thanks for the explanation.

**DonkeyOte** · 02-17-2010, 04:39 PM

No problem.

A far better explanation of the ISTEXT/ROW approach can be seen at the following link:

http://www.mrexcel.com/forum/showpos...0&postcount=13

Kudos as always to the author, PGC01 - responsible for countless jaw dropping solutions.