Re: Strip out text from cells

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi Rick

I download race cards from the web, I can't produce a whole race card but
hopefully the example below will appear ok (there does not seem to be a
facility to attach files here).

A B C D E
F G
1 Time Horse Name Horse Form Days since last run
(Stones) (Pounds)
2 1.10 Able Master 125548 78 11
13v
3 1.10 Alright Now 000127 45 11
12t
4 1.10 Heaven Sent 589034 15 10
12v1
5 1.10 Bold Show 112324 5
10 11
6 1.10 True Grit 054435 11
10 5b1
7 1.10 Chief Yeoman 785644 12 10
3t1
8 1:10 Gold Watch 870000 30 10
0v

Thus in column G row 2 (column G row 3 extract from column F etc) I would
like to extract the lbs in weight from column F, this will be either the
first two figures or just the single figure before the text and ignoring the
figure after the text.

I hope that this appears aligned okay.

Kind Regards

Paul





"Rick Rothstein" wrote:

I'm still not clear what you want. Examples are always best, so do this...
without explaining what your numbers mean to you, list several entries that
you can have in the cells you want to process and then give us a separate
list of what you want to have after you process those entries. Also, it
would be helpful if you tell us the column the original entries are in and
the column (or columns if you are producing 2 or more results from your
entries) you want the results to go in.

Something to keep in mind when posting a question on the newsgroups... while
you know exactly what you have to work with and what you want to do, we here
have no idea at all of either of these things... so you have to tell us in
detail... don't assume we can guess at any part of your problem.

--
Rick (MVP - Excel)


"Celticshadow" <Celticshadow@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:42501345-CF2F-451C-BC02-9C2BE95557C7@xxxxxxxxxxxxxxxx
Hi Rick

The figures are weights for horseracing and the the particular column
concerned is the lbs in weight (ie one column shows the weight in stones -
I
have that sorted - the other column is lbs ie 9st 5lbs). The column
concerned
sometimes has text in it as below

9t - 9lbs and the letter is an abbreviation for tongue tied.
11v - 11lbs and the letter stands for visored.
0b1 - 0 lbs and the letter stands for blinkered and the second number
stands
for first time, so in this case I just need the zero.

I need to transfer the first and/or second numbers before the text and
other
number. Is this possible, my apologies if I was not as clear first time
around but I am new on here, so a little green.

Kind Regards

Celticshadow




"Rick Rothstein" wrote:

Row and column ranges must be specified in ascending order. It is always
best to clearly state what you are trying to accomplish when you post a
question. Based on your examples, it looks like you want to remove the
leading number form the text in a cell. Here are a few ways to do that...

=MID(A1,LEN(LOOKUP(9.9E+307,--LEFT(A1,ROW($1:$99))))+1,99)

=SUBSTITUTE(A1, LOOKUP(9.9E+307,--LEFT(A1,ROW($1:$99))), "")

=MID(A3,MIN(FIND({"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"},UPPER(A3)&"ABCDEFGHIJKLMNOPQRSTUVWXYZ")),99)

--
Rick (MVP - Excel)


"Celticshadow" <Celticshadow@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7D2299FA-5213-4A5E-BCB2-C67E4F573623@xxxxxxxxxxxxxxxx
Hi

Below is an example of what I have done but excel keeps reversing the
formula back to g to h instead of h to g.

=MID(G5,1,FIND(h5:g5,1)-1) this what I enter.

=MID(G5,1,FIND(G5:H5,1)-1) this what i get.

Column A Col B Col C
Result

12T t =MID(G5,1,FIND(h5:g5,1)-1)
(#VALUE!)

Is there another way of stripping out the text (sometimes it might just
be
one nos and then text as 9v), any pointers would be much appreciated.

Kind Regards

Celticshadow











.

Relevant Pages

  • Re: Strip out text from cells
    ... Rick ... list of what you want to have after you process those entries. ... > concerned is the lbs in weight (ie one column shows the weight in> stones - ... my apologies if I was not as clear first time ...
    (microsoft.public.excel)
  • Re: Strip out text from cells
    ... I require G to return the weight in pounds from F (the first figure or two ... figures dependent on whether it is a single fig or double ie 1 lbs or 2lbs) ... "Rick Rothstein" wrote: ... list of what you want to have after you process those entries. ...
    (microsoft.public.excel)
  • Re: Strip out text from cells
    ... without explaining what your numbers mean to you, list several entries that you can have in the cells you want to process and then give us a separate list of what you want to have after you process those entries. ... concerned is the lbs in weight (ie one column shows the weight in stones - I ... have that sorted - the other column is lbs ie 9st 5lbs). ... my apologies if I was not as clear first time ...
    (microsoft.public.excel)
  • Re: Strip out text from cells
    ... concerned is the lbs in weight (ie one column shows the weight in stones - I ... my apologies if I was not as clear first time ... "Rick Rothstein" wrote: ... Rick (MVP - Excel) ...
    (microsoft.public.excel)
  • Re: OT: Ramashiva at 288 pounds
    ... I went to see my doctor today for the first time in two years, ... The last time I checked in with this doctor two years ago, ... > I can relate to the weight issue. ... > healthy diet, I gain weight very easily and have to force myself to ...
    (rec.gambling.poker)