Ajusting number based on test

[Rbartlem]

I have 2 columns. in column 1 I have information with how long I purchased/renewed a domain. I need column b to show just the # of years that are in column a. So b1 would have 1, b2 would have 2, b3 would have 1, b4 would have 2. Anyone have an idea of how to do this with a macro or a formula? Preferrably a formula if possible.

example:

             A                     B  
.com renewal 1 year       |                      | 
.com renewal 2 years      |                      | 
.net registration         |                      | 
.net registration 2 years |                      |

The acctual data looks like this ".COM Domain Name Renewal - 1 Year" but some only look like this... "Other Type Of Services". I tried to make this formula =RIGHT(A2,LEN(A2)-FIND("- ",A2)-1) which is leaving "1 Year" and I just need it to say "1" I also need it to just show a 1 instead of #VALUE! on other types.

 

[Domenic]

Try...

=TRIM(MID(A2,FIND("^^",SUBSTITUTE(A2,"-","^^",LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))))+1,SEARCH("year",A2)-FIND("^^",SUBSTITUTE(A2,"-","^^",LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))))-1))
..which will extract characters between the last hyphen and the word 'year'.

Hope this helps!

 

[Rbartlem]

I tried using your code on "Bulk Domain Name Registration (6-20) - 2 years" and it didn't work...

I used the below code and it worked perfect. I thought that the (1-1) had a hyphen in it but it didn't. I just needed to change it to search for a hyphen with a space and problem solved. Thank you very much!

=IF(A1>0,IF(ISERROR(MID(B1,SEARCH("- ",B1)+1,(SEARCH("Year",B1)-1)-SEARCH("- ",B1))+0),1,(MID(B1,SEARCH("- ",B1)+1,(SEARCH("Year",B1)-1)-SEARCH("- ",B1))+0)),"")

 

[Domenic]

I just tried my formula and it returns the number 2. What do you get?