Excel capture data in cell

Question

Dear MS,

I want to capture individual data from a cell (A2:A10), (D2:D10)

like the file as following link, please check the Yellow highlighted part is a result I want to capture

https://1drv.ms/x/s!AkQ_OBT745Y5my6fq106F6OGqP4U?e=7LhRuc

thx, Johnnie

Answer 1

Hi Johnnie,

possible solution:

B2: =--LEFT(A2,SEARCH(" ",A2)-1)

drag down.

To split a text it is easier to determine the delimiter positions in helper columns
E2: =SEARCH("x",D2)
F2: =SEARCH("x",D2,E2+1)

Now we can parse the values very easy:
G2: =--LEFT(D2,E2-1)
H2: =--MID(D2,E2+1,F2-E2-1)
I2: =--RIGHT(D2,LEN(D2)-F2)

J2: =G2*H2*I2

drag down.

BTW, the -- in front of the formulas is necessary to convert the text into numbers.

Andreas.

Answer 2

In general use the "Text to Columns" feature.
https://support.microsoft.com/en-us/office/split-text-into-different-columns-with-the-convert-text-to-columns-wizard-30b14928-5550-41f5-97ca-7a3e9c363ed7

Column A:

Column D:

Andreas.

Answer 3

Excel 365 Pro Plus with Power Pivot and Power Query.

Split columns invisibly with PQ.

Added extras to look like a business app,

using PivotTable, PivotChart and Slicer.

No formulas, no VBA macro.

https://www.mediafire.com/file/vjsabdrcjb4k50j/01_01_22.xlsx/file

https://www.mediafire.com/file/unk302g3z7bs6xw/01_01_22.pdf/file

Answer 4

Thx, Andreas

it is work, but

can I setup the formula in Column B and E:H instead of everytime delimit date..?? pls adv

B.rgds

Johnnnie