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!
HaydnWVN
Posted 2011-12-09T11:57:16.933
Reputation: 3 192
Are there things that could serve as column delimiters (tab charachters)? – soandos – 2011-12-09T12:20:21.437
Only groups of spaces, some cells (like the Acer p. d.) do not have the spaces between the name and size. All i need from the workbook is the names as the sizes are also repeated in other columns. – HaydnWVN – 2011-12-09T12:23:31.787
3If everything after 35 is "not name" then surely =LEFT(C2,35) would be enough? – Paul – 2011-12-09T13:08:50.793
2Also, why not import using fixed width columns and say column 1 is 35 characters wide? – Paul – 2011-12-09T13:09:49.823
Used your formula and passed it back to my source to check (it's 60,000 rows!). If you post your comment as an answer i'll accept it! :) – HaydnWVN – 2011-12-09T13:40:14.313
2Don't forget to trim off the trailing spaces: =TRIM(LEFT(C2,35)) – Tony Dallimore – 2011-12-09T15:20:55.657
Vote to close as it's resolved through Paul's and Tony's comments - yet nobody posted as an answer so i can't mark it as solved! Easy points if anyone wants them ;) – HaydnWVN – 2011-12-19T09:30:01.237
1@Paul. Although I added 10% to your answer, I think you should get the points for the original 90%. – Tony Dallimore – 2011-12-22T11:24:19.497
@TonyDallimore - thanks, I forgot about this question! I have answered. – Paul – 2011-12-22T14:24:32.630