[pcductape] Re: How to convert 1st word to last in a spreadsheet column

  • From: Scott McNay <Wizard@xxxxxxxx>
  • To: Carl <pcductape@xxxxxxxxxxxxx>
  • Date: Sun, 28 Dec 2003 12:24:53 -0600

Hi Carl,

Sunday, December 28, 2003, 6:35:24 AM, you wrote:

C> Hi Scott,

Ack!  I just realized that I answered your question incorrectly.  You
said you wanted the names to be "First & Second Last" format.  Here's
the formula, which doesn't care if they're single or married:

=TRIM(MID(A1,FIND(" ",A1),LEN(A1)) & " " & TRIM(LEFT(A1,FIND(" ",A1))))

You can then use the Special Copy to replace the formula with the
actual calculated value.

You'll still have trouble with unhyphenated multi-part last names, but
they're not too common.


C> You are really smart with spreadsheets.   Do you teach the stuff?   :-)

The Paste Special thing was something that I learned either here or
from another mailing list, earlier this year.

Pasting from a single cell to multiple cells is something that I
learned at work, also earlier this year.

The adjusting of formulas when cells are copied is something that I've
known for a while.

Excel formulas are basically the same as Visual Basic formulas, with
lots of specialty functions for speadsheet handling. I have lots of
experience with BASIC (waaaay back in the DOS days), little experience
with Visual Basic (although have done some VB scripts fairly
recently), but after the first few languages, learning another one is
fairly trivial, just a matter of hitting the references to find out
the exact wording of things, and so forth. BASIC uses "+" to
concatenate strings, while I learned earlier this year that VBS uses
"&" to concatenate strings. I tried that in the Excel formula, and it
worked. It took me a while to hunt down the FIND() function; BASIC
uses INSTR(), and Clipper/xBase++ (similar to BASIC, but with commands
and functions for database handling) use AT(), and I have no idea what
VB uses, but it probably uses FIND() also. For trimming, Clipper uses
LTRIM() and RTRIM(), and Xbase++ has both of those plus ALLTRIM(), and
I don't recall that BASIC had anything standard for the purpose; I
tried TRIM() and it worked. MID() and LEFT() both come from BASIC.
MID() was a bit of a pain, since it wants to know how many characters
to copy, instead of defaulting to (Len-Start+1).

--Scott.


To unsubscribe from this list send an email to
pcductape-request@xxxxxxxxxxxxx with 'unsubscribe' in the Subject field
OR by logging into the Web interface. 

To view the message archives simply go to: 
http://www.freelists.org/archives/pcductape/

Other related posts: