Convert text string, to single number based on number in string.

**traveler911** · 01-04-2018, 11:08 PM

I am attempting to convert a field with a sting to an interger based off the first number after the hypen.

17-103456 -> 1
17-123456 -> 1
17-201265 -> 2
17-202222 -> 2

I have tried using =TEXT but I haven't been to able get the right formula.

I would appreciate any help you could provide.

**Norie** · 01-04-2018, 11:16 PM

Try this.

=MID(A1,SEARCH("-",A1)+1,1)

Note, this will return text value, if you need to convert it to a numeric value try one of these.

=MID(A1,SEARCH("-",A1)+1,1)+0

=MID(A1,SEARCH("-",A1)+1,1)*1

=VALUE(MID(A1,SEARCH("-",A1)+1,1))

**traveler911** · 01-05-2018, 12:31 AM

the top one worked like a charm.

Thanks so much for the help. saves me from going through 3600 records.

Kent

Convert text string, to single number based on number in string.

LinkBack

Thread Tools

Rate This Thread

Display

Convert text string, to single number based on number in string.

Re: Convert text string, to single number based on number in string.

Re: Convert text string, to single number based on number in string.

Thread Information

Users Browsing this Thread

Similar Threads

Pasted text string appears as number with ten decimal places - How do I convert back ?

Generating random number string based on Alphanumeric String

Convert Number to Text string

How to convert a text string to a number?

My worksheet won't convert a number string to a shorter number string

RE: How to COnvert text string to number

[SOLVED] Re: How to COnvert text string to number

Bookmarks

Bookmarks

Posting Permissions