How to extract 2 value from a cell

Ciccio,

Here's a solution that treats the combined value as text and creates two separate values from it.

Expressions for the first part and second part are shown.

Expression for first part is: =VALUE(LEFT(A2,FIND("-", A2)−1))

Expression for second part is: =VALUE(RIGHT(A2,LEN(A2)−(FIND("-", A2)−1)−1))

Jerry

Hi CiccioPolenta,

The functions TEXTBEFORE and TEXTAFTER are you friends.

Formula in B2 is TEXTBEFORE(A2,"-")

Formula in C2 is TEXTAFTER(A2,"-")

More information:

Type = in a cell to bring up the Function Browser.

Type "text" in the search box to see a list of the various TEXT functions..

The results will be Text.

As Jerry said, use the VALUE function to convert Text to Number.

Or multiply by 1 Text that looks like a Number.

Regards,

Ian.

Here's another way:

select cell B2 and type, or copy and paste from here, then formula:

=REGEX.EXTRACT($A2,"([0-9]{5})-([0-9]{4})",1,1)

shorthand for this is:

B2=REGEX.EXTRACT($A2,"([0-9]{5})-([0-9]{4})",1,1)

C2=REGEX.EXTRACT($A2,"([0-9]{5})-([0-9]{4})",1,2)

This solution uses regular expressions and capture groups.

then RegEx "([0-9]{5})-([0-9]{4})"

describes a patter where are are two groups of numeric digits separated by a dash. The group on the left has five digits and the one on the right side has four digits. The parenthesis around the digits is capture notation.

Ian,

TEXTBEFORE AND TEXTAFTER are great timesavers in this case. They sure beat using LEFT, RIGHT, FIND and LEN for beginners. For me, they're burned into my brain. I should make an effort to switch.

Jerry

Hi Jerry,

Seeing Wayne's reply using REGEX, I sympathise with you. REGEX is way above my salary level. I have used BASIC, ALGOL, Fortran, GenStat, Unix, HyperTalk, MSDOS, SPlus (actually R). I do not want to learn yet another programming language!

Happy New Year and Regards,

Ian.