Parsing Text

  • Thread starter Thread starter Craig
  • Start date Start date
C

Craig

I hae a line of text that has the abbreviation CKT in it
with a 1-2 digit number just before it. The problem is the
abbreviation CKT can fall at the beginning, middle or end.
What formuala or worksheet function can I use to extract
the 1-2 digit number before the abbrev. CKT?

TIA, Craig
 
You can use FIND() to find the position of the text:

=FIND("CKT",A1)

What you do with it depends on the nature of your "line of text". If a 1
digit number always has a space in front of it (or if the number is the
first character), then this will work:

=--TRIM(MID(" " & A1,FIND("CKT",A1)-1,2))

but will fail if your text is like abc1CKT123
 
The function =MID(" " & A1,FIND("CKT",A1)-1,2) worked
great! I also noticed that CKT is also spelled Ckt or ckt.
How do I make all three the same spelling (CKT) so the
formula works all the time?
 
=LEFT(A1,FIND("CKT",UPPER(A1))-1

----- Craig wrote: ----

The function =MID(" " & A1,FIND("CKT",A1)-1,2) worked
great! I also noticed that CKT is also spelled Ckt or ckt.
How do I make all three the same spelling (CKT) so the
formula works all the time
-----Original Message----
You can use FIND() to find the position of the text
=FIND("CKT",A1
What you do with it depends on the nature of your "line
Click to expand...
of text". If a 1
digit number always has a space in front of it (or if the number is the
first character), then this will work
Click to expand...
 
To make the formula work all the time, just change the "Find" function
(which is case sensitive) to the "Search" function (*not* case sensitive).
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

The function =MID(" " & A1,FIND("CKT",A1)-1,2) worked
great! I also noticed that CKT is also spelled Ckt or ckt.
How do I make all three the same spelling (CKT) so the
formula works all the time?
 
Back
Top