Custom Function

[Maltraca1]

I am trying to locate or create a function which reads the number from a string.

The data in the cell is formatted as text in this fashion: Name-25

I would like to return in a new cell just the value of 25. How might I go about this?

[Ron Coderre]

Both of these work:

=MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),99)*1
Confirm with [Enter]


=--(MID(A1,MATCH(FALSE,ISERROR(MID(A1,ROW($1:$100), 1)*1),0),100))
Confirm with [Ctrl]+[Shift]+[Enter]

Does that help?

Ron

[swatsp0p]

Good examples, Ron. Other options:

=RIGHT(A1,LEN(A1)-FIND("-",A1)) will work for your example of 'name-25', and returns the value also as text.

=RIGHT(A1,(LEN(A1)-FIND("-",A1)))*1 same as above, but make the result a numeral, not text.

TEXT TO COLUMNS: If you have several cells in a single column and each uses the same separator between text and numbers (as in your example: a single dash) TEXT TO COLUMNS will work faster for you.

The best answer will depend on the exact format of your data, how many cells and where they are in your worksheet.

Try these or give us more info.

Good Luck.

Bruce

[Maltraca1]

Thanks,

This:

=RIGHT(A1,(LEN(A1)-FIND("-",A1)))*1

was exactly what I was looking for.

[swatsp0p]

Thanks for the feedback, it is always appreciated. Glad we were able to help.

Cheers!

Bruce