# formula to extract numbers from within a cell

1. ## formula to extract numbers from within a cell

We have data that comes into excel with 2 or 3 number sets in a cell. For example 10-9 or 8-12-3 would be in the cell as text. Need formulas to extract each set of numbers separated by the dashes into its own column. Sometimes there is 1 number in the number set, sometimes 2. Sometimes there is not a 3rd set of numbers in the cell and the last formula would need to return a 0 if the cell only has 1 dash (meaning only 2 sets of numbers).

The first formula for the 1st set of numbers i got
On the second formula I can't figure out how to get just the numbers to the right of the 1st dash and nothing else
On the 3rd formula I am completely lost

2. ## Re: formula to extract numbers from within a cell

Try this in L3:

=TRIM(MID(SUBSTITUTE("-"&\$J3,"-",REPT(" ",100)),COLUMNS(\$L3:L3)*100,100))

3. ## Re: formula to extract numbers from within a cell

Thank you but it seems that the formula I had in L3 was working fine. My problem is I need a working formula for M3 and N3

4. ## Re: formula to extract numbers from within a cell

Originally Posted by RICK JAMES
the formula I had in L3 was working fine.
If there is only a number in J3, the formula will return an error.

5. ## Re: formula to extract numbers from within a cell

Also, you can use "Text to Columns" with the separator: "-"

6. ## Re: formula to extract numbers from within a cell

L3:
Formula:
`Please Login or Register  to view this content.`

M3:
Formula:
`Please Login or Register  to view this content.`

N3:
Formula:
`Please Login or Register  to view this content.`

Select L3:N3 and fill down as far as needed.

Could J3 ever contain just 1 section and no hyphens?

7. ## Re: formula to extract numbers from within a cell

Formulas for L3 and M3 do the trick. The formula for N3 gives everything as a tenth of the number ie .3 instead of 3. Not sure how to fix that.

8. ## Re: formula to extract numbers from within a cell

Sorry.

N3:
Formula:
`Please Login or Register  to view this content.`

9. ## Re: formula to extract numbers from within a cell

Formula for L3 = VALUE(LEFT(SUBSTITUTE(J3,"-",REPT(" ",100)),100))
For M3 = VALUE((RIGHT(LEFT(SUBSTITUTE(J3,"-",REPT(" ",100)),200),100)))
For N3 = IF(LEN(J3)=LEN(F3)+LEN(G3)+1,0,VALUE((RIGHT(SUBSTITUTE(J3,"-",REPT(" ",100)),100))))

10. ## Re: formula to extract numbers from within a cell

An alternative solution based upon Power Query/Get & Transform found on the Data Tab of the Ribbon

``Please Login or Register  to view this content.``
Excel 2016 (Windows) 32 bit
A
B
C
1
Column1.1 Column1.2 Column1.3
2
21
3
1
3
12
18
2
4
5
16
0
5
20
3
1
6
22
6
0
7
9
17
0
 Sheet: Sheet2

Review PQ
In the attached file
Click on any cell in the new table
On the Data Tab, click on Queries & Connections
In the right window, double click to open Query
Review PQ steps

M-code basics:
- "let" is the start of a query
- "in" is the closing of a query
- each transformation step sits in between those 2 lines
- each step line is ended by a comma, except the last one
- "Source" is always the first step (Source Data)
- After "in" you have the last step referenced

11. ## Re: formula to extract numbers from within a cell

No apology necessary. Thank you!

12. ## Re: formula to extract numbers from within a cell

Thank you all. I guess with excel it just comes down to more than one way to skin the xlsx. Not sure why one would be better than another but I will take the working formulas.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1