removing last 16 digits of the value of a cell

[destrolennox]

Hello,

I have a quick question that I think can be easily answered, I am just struggling to get it to work correctly.

I have a list of names and account numbers. The cells like like the below example:

Smith, John M. xxxxxx4023820886

The "X"s were not put in by me, they actually exist like that in the spreadsheet.

I would like to remove the 16 digit account number. Each name obviously contains a different number of characters which is making this difficult for me.

Thanks for the help,

Brendan

[VBA Noob]

Looks like you could use Text to columns

Data > Text to Columns

Click delimter and use Other with the aposthophe sign ","

Otherwise you could try =right(A1,16)

VBA Noob

[SimonCC]

Try:
=LEFT(A1,LEN(A1)-16)

-Simon

"destrolennox" wrote:

>
> Hello,
>
> I have a quick question that I think can be easily answered, I am just
> struggling to get it to work correctly.
>
> I have a list of names and account numbers. The cells like like the
> below example:
>
> Smith, John M. xxxxxx4023820886
>
> The "X"s were not put in by me, they actually exist like that in the
> spreadsheet.
>
> I would like to remove the 16 digit account number. Each name
> obviously contains a different number of characters which is making
> this difficult for me.
>
> Thanks for the help,
>
> Brendan
>
>
> --
> destrolennox
> ------------------------------------------------------------------------
> destrolennox's Profile: http://www.excelforum.com/member.php...o&userid=36503
> View this thread: http://www.excelforum.com/showthread...hreadid=562652
>
>

[MDubbelboer]

Try:
=LEFT(A1,LEN(A1)-16)

well take 17 to get rid of the space
=left(a1,len(a1)-17)
or
=trim(left(a1,len(a1)-16)

[destrolennox]

Thanks for the help, i used the TRIM function and it worked perfectly