I would like to extract the numerical values in the middle of a cell and have it in separate columns. There are usually two numbers between the texts, and I would like to have them separated such as table 3 below. If there is only one value, it would go to the "From" Column.
I am currently using the delimiter and separating each of the texts by space or by "-". It worked to a certain extent except that some of the columns with the numerical values contained text as well as seen in table 2. The sample data is in Table 1.
I tried using the LEFT function, but I don't think I'm using it correctly. Is there a way to get it to look like Table 3?
Thank you!
Table 1. Sample data
| Name |
|---|
| abcd 29.1 42.3 (1PRO) |
| 620001 T abcd 32.6 41 (3ea) |
| 600121 abcd cross 74.0 (Crea) |
Table 2.
| Name | From | To | ||||
|---|---|---|---|---|---|---|
| abcd 29.1 42.3 (1PRO) | abcd | 29.1 | 42.3 | (1PRO) | ||
| 620001 T abcd 32.6 41 (3ea) | 620001 | T | abcd | 32.6 | 41 | (3ea) |
| 600121 abcd cross 74.0 (Crea) | 600121 | abcd | cross | 74.0 | (crea) |
Table 3.
| Name | From | To |
|---|---|---|
| abcd 29.1 42.3 (1PRO) | 29.1 | 42.3 |
| 620001 T abcd 32.6 41 (3ea) | 32.6 | 41 |
| 600121 abcd cross 74.0 (Crea) | 74.0 |
CodePudding user response：
In Windows Excel 2016, you can use the FILTERXML function and an xPath argument that returns only numeric values excluding the first node.
- Construct XML:
"<t><s>" & SUBSTITUTE($A21," ","</s><s>") & "</s></t>" - the
xPath:
"//s[position()>1 and number(.)=number(.)]"
If you had dynamic arrays, that would be all you need. But since you don't you need to wrap into an INDEX function to extract the individual elements.
If there are at most, two elements, you can hard code the "row" entry; if not, you will need to use a dynamic function to generate the rows.
Below I show the dynamic function which will increment by one for each column you "drag" the formula to the right.
=IFERROR(INDEX(FILTERXML("<t><s>" & SUBSTITUTE($A1," ","</s><s>") & "</s></t>","//s[position()>1 and number(.)=number(.)]"),COLUMNS($A:A)),"")
CodePudding user response：
I always solve this sort of issue by doing the following.
Create a custom function with the following code in a new module.
If you've never done that before, then follow this link ... 
Voila! I hope that helps.