Strip out text from cells

  • Thread starter Thread starter Celticshadow
  • Start date Start date
C

Celticshadow

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
 
Why are you using a colon between H5 and G5? Use a comma to separate
parameters:

=MID(G5,1,FIND(h5,g5,1)-1)

Hope this helps.

Pete
 
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)
 
Hi Pete

Thanks.

It certainly works for a cell that contains text and numbers but when a cell
contains a number such as 0 or 10 or 11 on it's own it just returns the value
error after I auto fill.

Are you aware how I could manage that scenario. Apologies for seeming a
little dim but I am no PC whiz.

Kind Regards

Celticshadow
 
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
 
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.
 
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
 
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
 
Hi Rick

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)
as below.

F
Weight (Pounds)
13v
12t
12v1
11
5b1
3t1
0v

G to return
13
12
12
11
5
3
0

Nothing to be returned in H.

Kind Regards

Celticshadow
 
This is what you want...

G2: =LOOKUP(9000000000+307,--LEFT(A2,ROW($1:$99)))

Copy it down as necessary.
 
Hi Rick

Many many thanks for your help, that formula works a treat and much
appreciated. I shall in future try to make things somewhat clearer when
posing a question.

Thanks Again

Kind Regards

Celticshadow
 
Hi Pete

Although your solution was not quite what I was looking for, which is mainly
due to me not explaining things well enough, it is still very helpful. Thanks
very much.

Kind Regards

Celticshadow
 
Back
Top