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

  • From: "Carl" <ctm007@xxxxxxxxxxxxxx>
  • To: <pcductape@xxxxxxxxxxxxx>
  • Date: Sun, 28 Dec 2003 12:39:10 -0600

Hi Scott,

Thanks.

Carl

----- Original Message ----- 
From: "Scott McNay" <Wizard@xxxxxxxx>
To: "Carl" <pcductape@xxxxxxxxxxxxx>
Sent: Sunday, December 28, 2003 12:24 PM
Subject: [pcductape] Re: How to convert 1st word to last in a spreadsheet
column


>
> 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/

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: