ExcelBanter - Stripping data

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Stripping data (https://www.excelbanter.com/excel-programming/290056-stripping-data.html)

How best to achieve this, please:

data is of the type 999 102A and is the only entry
in a cell.

I would like to strip the data to end up with 2 options,
either 102A or just A.

Regards.

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.576 / Virus Database: 365 - Release Date: 30/01/2004

Hi Stuart
to get '102A' try
=RIGHT(A1,4)

to get 'A'
=RIGHT(A1,1)

but I'm sure I have missed something :-)
Frank

Stuart wrote:
How best to achieve this, please:

data is of the type 999 102A and is the only entry
in a cell.

I would like to strip the data to end up with 2 options,
either 102A or just A.

Regards.

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.576 / Virus Database: 365 - Release Date: 30/01/2004

Thanks for that.
My best guess about the data is that 999 is some sort of
Job reference, 102 is a page number, and A is an item
reference.
999 could equally be 9 or 99, but since there is a space
before the page/item reference, then I could remove the
99whatever reference using Split... so I'd then be left
with 102A which would be the 1st solution.

But 102A could equally be 1A, 15A or 999A, and I
will not know which. As best I can tell, though, is that
it will always be a sequence of numbers ending in a
single capital letter.......how do I isolate that letter?

Regards and thanks

"Frank Kabel" wrote in message
...
Hi Stuart
to get '102A' try
=RIGHT(A1,4)

to get 'A'
=RIGHT(A1,1)

but I'm sure I have missed something :-)
Frank

Stuart wrote:
How best to achieve this, please:

data is of the type 999 102A and is the only entry
in a cell.

I would like to strip the data to end up with 2 options,
either 102A or just A.

Regards.

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.576 / Virus Database: 365 - Release Date: 30/01/2004

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.576 / Virus Database: 365 - Release Date: 30/01/2004

Right(split("999 102A"," ")(1),1)

From the immediate window:

? Right(split("999 102A"," ")(1),1)
A

? Right(split("9 15A"," ")(1),1)
A

--
Regards,
Tom Ogilvy

"Stuart" wrote in message
...
Thanks for that.
My best guess about the data is that 999 is some sort of
Job reference, 102 is a page number, and A is an item
reference.
999 could equally be 9 or 99, but since there is a space
before the page/item reference, then I could remove the
99whatever reference using Split... so I'd then be left
with 102A which would be the 1st solution.

But 102A could equally be 1A, 15A or 999A, and I
will not know which. As best I can tell, though, is that
it will always be a sequence of numbers ending in a
single capital letter.......how do I isolate that letter?

Regards and thanks

"Frank Kabel" wrote in message
...
Hi Stuart
to get '102A' try
=RIGHT(A1,4)

to get 'A'
=RIGHT(A1,1)

but I'm sure I have missed something :-)
Frank

Stuart wrote:
How best to achieve this, please:

data is of the type 999 102A and is the only entry
in a cell.

I would like to strip the data to end up with 2 options,
either 102A or just A.

Regards.

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.576 / Virus Database: 365 - Release Date: 30/01/2004

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.576 / Virus Database: 365 - Release Date: 30/01/2004

Hi Stuart
try the following:
1. For the page number:
=MID(A1,FIND(" ",A1)+1,LEN(A14)-FIND(" ",A1)-1)
2. For the reference:
=RIGHT(A1,1)

HTH
Frank

Stuart wrote:
Thanks for that.
My best guess about the data is that 999 is some sort of
Job reference, 102 is a page number, and A is an item
reference.
999 could equally be 9 or 99, but since there is a space
before the page/item reference, then I could remove the
99whatever reference using Split... so I'd then be left
with 102A which would be the 1st solution.

But 102A could equally be 1A, 15A or 999A, and I
will not know which. As best I can tell, though, is that
it will always be a sequence of numbers ending in a
single capital letter.......how do I isolate that letter?

Regards and thanks

"Frank Kabel" wrote in message
...
Hi Stuart
to get '102A' try
=RIGHT(A1,4)

to get 'A'
=RIGHT(A1,1)

but I'm sure I have missed something :-)
Frank

Stuart wrote:
How best to achieve this, please:

data is of the type 999 102A and is the only entry
in a cell.

I would like to strip the data to end up with 2 options,
either 102A or just A.

Regards.

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.576 / Virus Database: 365 - Release Date: 30/01/2004

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.576 / Virus Database: 365 - Release Date: 30/01/2004

Many thanks to you both.

Apologies for the delay, but my ISP was in difficulty.....
(again).

Regards.

"Tom Ogilvy" wrote in message
...
Right(split("999 102A"," ")(1),1)

From the immediate window:

? Right(split("999 102A"," ")(1),1)
A

? Right(split("9 15A"," ")(1),1)
A

--
Regards,
Tom Ogilvy

"Stuart" wrote in message
...
Thanks for that.
My best guess about the data is that 999 is some sort of
Job reference, 102 is a page number, and A is an item
reference.
999 could equally be 9 or 99, but since there is a space
before the page/item reference, then I could remove the
99whatever reference using Split... so I'd then be left
with 102A which would be the 1st solution.

But 102A could equally be 1A, 15A or 999A, and I
will not know which. As best I can tell, though, is that
it will always be a sequence of numbers ending in a
single capital letter.......how do I isolate that letter?

Regards and thanks

"Frank Kabel" wrote in message
...
Hi Stuart
to get '102A' try
=RIGHT(A1,4)

to get 'A'
=RIGHT(A1,1)

but I'm sure I have missed something :-)
Frank

Stuart wrote:
How best to achieve this, please:

data is of the type 999 102A and is the only entry
in a cell.

I would like to strip the data to end up with 2 options,
either 102A or just A.

Regards.

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.576 / Virus Database: 365 - Release Date: 30/01/2004

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.576 / Virus Database: 365 - Release Date: 30/01/2004

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.576 / Virus Database: 365 - Release Date: 30/01/2004