2

I have used basic IF ISNUMBER functions before to identify parts of cells and return the value in a different cell, such as:

=IF(ISNUMBER(VALUE(RIGHT(B5,1))),"",IF(ISNUMBER(VALUE(MID(B5,LEN(B5)-1,1))),RIGHT(B5,1),RIGHT(B5,2)))

How can i adapt this formula to look for a number/txt value and strip it out? Or would it be better to write new formula? Feel free to tell me where i've gone wrong previously! ;)

Note: the above formula isn't for the data below

Here is an example of the cell contents, I want to strip out the sizes (3L, 10L etc), the 'BARE ROOT' / 'ROOT BALL' / 'FEATHERED' and Girth sizes leaving only the names remaining.

ACANTHUS spinosus                  3L
ACANTHUS spinosus                  3L
ACER campestre                     10L
ACER campestre                     3L
ACER campestre                     BARE ROOT           14/16GIRTH
ACER campestre                     BARE ROOT 150/175cm
ACER campestre                     BARE ROOT 30/40cm
ACER campestre                     BARE ROOT 40/60cm
ACER campestre                     BARE ROOT 60/80cm
ACER campestre                     BARE ROOT 80/100cm
ACER campestre                     BARE ROOT 80/100cm
ACER campestre                     BARE ROOT 80/100cm
ACER campestre                     BARE ROOT 80/100cm
ACER campestre 'Elsrijk'           ROOT BALL           10/12GIRTH
ACER campestre 'Elsrijk'           ROOT BALL           14/16GIRTH
ACER japonicum 'Aconitifolium'     10L       125/150cm
ACER japonicum 'Aconitifolium'     10L       60/80cm
ACER negundo 'Flamingo'            50L       200/250cm
ACER p. d. 'Dissectum Atropurpurea'70L                 16/18GIRTH
ACER p. d. 'Dissectum Palmatifidum'25L                 FEATHERED
ACER p. d. 'Dissectum Palmatifidum'25L                 FEATHERED

More info
Looking through the sheet, the maxium name length is 35 characters, anything after that is size related and stripable, so some code to strip out after character 35 would be enough, then something to remove instances of more than 1 space would suffice...

Method 1
I've used {=LEFT(C2, MATCH(TRUE,ISNUMBER(1*MID(C2, ROW($1:$65), 1)),0)-1)} which is close to what i need, now just a way to identify the BARE ROOT and ROOT BALL, C2 is my cell... Also want it to not report 'ERROR' when no number is found... :)

Method 2 (Current Solution)
=LEFT(C2,35) as suggested by Paul below!

9
  • Are there things that could serve as column delimiters (tab charachters)?
    – soandos
    Dec 9, 2011 at 12:20
  • 3
    If everything after 35 is "not name" then surely =LEFT(C2,35) would be enough?
    – Paul
    Dec 9, 2011 at 13:08
  • 2
    Also, why not import using fixed width columns and say column 1 is 35 characters wide?
    – Paul
    Dec 9, 2011 at 13:09
  • 2
    Don't forget to trim off the trailing spaces: =TRIM(LEFT(C2,35)) Dec 9, 2011 at 15:20
  • 1
    @Paul. Although I added 10% to your answer, I think you should get the points for the original 90%. Dec 22, 2011 at 11:24

1 Answer 1

2

This question was answered in the comments and was collaborative, but here is my answer combined with Tony's:

=TRIM(LEFT(C2,35))

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .