stripping number at end 1

[mart10]

I am using Excel 2003

I have a column of data in general format

I want to strip number at end of data( and the leading space if possible)

eg
ROYAL BANK OF SCOTLAND 7011976
SOCIETE GENERALE LONDO 7012727

to

ROYAL BANK OF SCOTLAND
SOCIETE GENERALE LONDO


The no part is allways 7 charaters

 

[ClulessChris]

look at Data > Text to Columns

Never knock on Death's door: ring the bell and run away! Death really hates that!

 

[xlhelp]

=LEFT(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")-1))


Avoid Hangovers. Stay drunk.

 

[Salut39]

Hi xlhelp,

Great formula, could you please explain the SEARCH criteria and "{}".

Thanks
Yuri

 

[SkipVought]

{} delimits an array (0-9)

So the SEARCH function looks for any occurrence of 0-9 in the string with digits 0-9 concatenated.

The MIN function returns the minimum of the search values, which would locate "7" in each of your 2 examples, which in both cases is the 23rd character accomodating for the SPACE.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BlueHorizon]

You can use the Text to Columns feature as suggested above, and then use the TRIM function (=TRIM(A1) to remove the space.

Hope this helps,

Best,
Blue Horizon

 

[xlhelp]

CluelssChris and BlueHorizon.

I am probably missing something somewhere. I tried using the Text to Column; but, I cannot do it without breaking up the text. Would you kindly outline the steps? Thanks.


Avoid Hangovers. Stay drunk.

 

[SkipVought]

I agree!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[randy700]

Am I missing something here or is everyone over complicating this?
If there are always 7 digits following a space at the end, why not simply this?

=left(A1,Len(A1)-8)

Randy

 

[byundt]

Some banks in the US have a number as part of their name. For example, 1st Bank Yuma. Any numbers that are part of the name of the bank will cause a problem for the formula using SEARCH.

You can avoid such problems by testing the last 7 characters for a number directly:

=LEFT(A1,LEN(A1)-IF(ISNUMBER(--RIGHT(A1,7)),8,0))
=TRIM(LEFT(A1,LEN(A1)-IF(ISNUMBER(--RIGHT(A1,7)),7,0)))

The second formula using TRIM handles the possibility that the space might be omitted preceding the seven digits at the end.

Brad