Re: Strip out text from cells

Nope, the alignment did not come out okay. However, you provided more information that is needed to solve your problem. All we need to see is the column of data you want to work with and the result you would like to see... all the other columns, while they have meaning to you, are just "noise" to us. For example, this is the column of information you want to change...

F <<=this may be the wrong actual column
13v
12t
12v1
5b1
3t1
0v

What we need to see (preferably on the same line) is what you want to pull from this. For example, maybe this (modify it for us in the way you actually want it)?

F G H
13v 13 v
12t 12 t
12v1 12 v1
5b1 5 b1
3t1 3 t1
0v 0 v

--
Rick (MVP - Excel)


"Celticshadow" <Celticshadow@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:0DE7F7DB-DC50-4E5A-A9AF-670AFDF49978@xxxxxxxxxxxxxxxx
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 Rothstein" wrote: ... 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
    ... 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
    ... Rick (MVP - Excel) ... I require G to return the weight in pounds from F (the first figure or two ...
    (microsoft.public.excel)
  • Poker Nightmare
    ... This was weird because it was the first time I ever actually jumped out of ... There's a tall skinny guy I've never seen before in seat 2. ... Rick folds and the two of them call. ...
    (rec.gambling.poker)
  • Re: non selectable table values
    ... The fact that the allowed entries are not in sequence won't matter. ... > Thanks Rick, those are great suggestions. ... > You're 2nd suggestion wouldn't work b/c I DO need to be able to allow ... >> I would probably use a validation rule to specify that the field cannot ...
    (microsoft.public.access.tablesdbdesign)
Quantcast