Delete part of string starting with small letter

[anupamtiwari05]

Hi Excel Ninjas and All,


Happy New Year.


I need your help to resolve my query.


I have very weird data in column A explained as below:


1. 195 AS U320 IAF-CHKDISK V2.0 Checklist

2. 200 DF AS JHK C2 CDIndia


String to be deleted from above

1. Checklist

2. India


The remaining string should be left in column A.


I can not upload sample data being some restrictions.


I have searched websites but not found any exact solution that can resolve my issue.


Please let me know if you want more info to make my query clear to you all.


Thanks & Regards,

Anupam

 

[Debraj]

Hi Anupam..


Can you please try the below..

=LEFT(A1,MIN(IFERROR(FIND(CHAR(ROW($A$97:$A$122)),A1),""))-2)

with Ctrl + Shift + Enter, Not just Enter..


where Text is in A1..


Regards,

Deb

 

[anupamtiwari05]

Hi Deb,


You are genius.


Its working absolutely fine for me.


Thanks a lot for your assistance.


The Forum Rocks as always.


Thanks & Regards,

Anupam

 

[vijay.vizzu]

Dear Debraj roy,


I am very much impressed with your formula, can you please explain the formula " How it works"?

 

[Debraj]

Hi Vijay,


Thanks for the kind words..

Lemme try..

[list type=decimal]
[*] CHAR(ROW($A$97:$A$122)

Its just a list of small Letter "a to z".. means what I want to find.

Char(97) = a, Char(99) = c.. and char(122) = z
[*] FIND("a to z",A1)

Its search for 1st match in A1, where a/b/c.../z found, and gives its position..

So assume character "j,o,p" found at 27,31,45 th position.
MIN(IFERROR(FIND(CHAR(ROW($A$97:$A$122)),A1),""))

It will pick only 27 from the above, as smallest.. means 1st found small letter charcter..
[/list type=decimal]

I hope rest you can able to figure out..


and dont forget to look the below topic also..

http://chandoo.org/wp/2012/06/19/extract-numbers-from-text-excel/


Regards,

Deb