Extract Data @ MID

[jns1087]

Hi i have this data in Cell A1 that I'm trying to split which i cant use a delimiter due to its limits as far as i know.
A1
33000+5000+200*2

in B1 i made a formula that results into 33000
=VALUE(LEFT(A1,SEARCH("+",A1)-1))

in C1 i made a formula that extract the data 5000
=VALUE(MID(A1,SEARCH("+",A1)+1,SEARCH("+",A1,SEARCH("+",A1)+1)-SEARCH("+",A1)-1))

for the 3rd value of 200 i tried this way
in D1
=SEARCH("+",A1)
for E1
=SEARCH("+",A1,D1+1)
for F1 it gives me the result of 200*2, if i turn the data to 2200*2 the result gives 2200*2
=MID(A1,E1+1,E1-D1)

i just want the data in between of the 2nd + until * which is 200 or any data i input from A1, i cant have it work, appreciated any help


For the value of 2 since its always *2 i made using right for G1
=RIGHT(A1,1)


Thanks,
Jerwin

 

[Kerryx]

 

[denzo36]

Why not use substitute to replace the + and * to the same delimiter e.g. a comma like below and then use text to columns to split by that comma afterwards?

=SUBSTITUTE(SUBSTITUTE(A1,"+",","),"*",",")

 

[FormR]

Hi, there's a formula here that you could also employ.

Get Field from Delimited Text String

How many times have you had a delimited text string (say "one,two,three,four" where the delimiter is a comma) and you wanted a formula to retrieve one of the delimited values, say, the third one. Personally, I have wondered for years why such a function is not built into Excel but, alas, it...

www.excelfox.com

For example:

Book2
	A	B	C	D	E
1	33000+5000+200*2	33000	5000	200	2
Sheet1

Cell Formulas
Range	Formula
B1:E1	B1	=TRIM(MID(SUBSTITUTE(SUBSTITUTE($A1,"*","+"),"+",REPT(" ",99)),COLUMNS($B1:B1)*99-98,99))

 

[Peter_SSs]

I suggest that you investigate XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be.

If your data is always of a similar format, then could you use this to extract all the values?
Edit: @FormR posted while I was composing my post. Very similar formulas but note that one returns text and one returns numbers.

23 07 13.xlsm
	A	B	C	D	E
1	33000+5000+200*2	33000	5000	200	2
2	5+6+22000*2	5	6	22000	2
Sheet2 (2)

Cell Formulas
Range	Formula
B1:E2	B1	=MID(SUBSTITUTE(SUBSTITUTE("+"&$A1,"*","+"),"+",REPT(" ",100)),COLUMNS($B:B)*100,100)+0

 

[johnpetersen]

To extract the value between the second "+" sign and the "*" symbol in cell A1, you can use the following formula:
=VALUE(MID(A1,SEARCH("+",A1,SEARCH("+",A1)+1)+1,SEARCH("*",A1)-SEARCH("+",A1,SEARCH("+",A1)+1)-1))
This formula builds upon your existing approach and adds an additional nested SEARCH function to find the position of the second "+" sign. It then uses MID to extract the substring between the second "+" and the "*", and VALUE to convert it to a numeric value. cat mario

So in cell D1, you can use the formula provided above to extract the value "200" from the example you provided.

 

[jns1087]

View attachment 95179
View attachment 95180
View attachment 95181

wish i could, but there are some text string everywhere on the columns, i leave the data as is.

 

[jns1087]

thanks all, before i saw this thread again i did it by this, not optimize or efficient, but yeah.

DELIMITER BY MATHEMATICAL SYMBOLS.xlsm
	A	B	C	D	E	F	G	H	I	J
1	DATA	1ST	2ND	3RD	4TH		POSITION OF 1ST +	POSITION OF 2ND +	POSITION OF *	HELPER
2	400+20+20*2	400	20	20	2		4	7	10	20*2
Sheet1

Cell Formulas
Range	Formula
B2	B2	=VALUE(LEFT(A2,SEARCH("+",A2)-1))
C2	C2	=VALUE(MID(A2,SEARCH("+",A2)+1,SEARCH("+",A2,SEARCH("+",A2)+1)-SEARCH("+",A2)-1))
D2	D2	=VALUE(LEFT(J2,FIND("*",J2)-1))
E2	E2	=RIGHT(A2,1)
G2	G2	=SEARCH("+",A2)
H2	H2	=SEARCH("+",A2,G2+1)
I2	I2	=FIND("*",A2)
J2	J2	=MID(A2,H2+1,I2-G2)

and also i redo the formula of peter by this formula with new test data reference.

DELIMITER BY MATHEMATICAL SYMBOLS.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	400+40+40*2+500+50+50*2-(300+30+30*2)	400	40	40	2	500	50	50	2	300	30	30	2
Sheet2

Cell Formulas
Range	Formula
B1:M1	B1	=IFERROR(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("+"&$A1,"*","+"),"-","+"),"("," "),")","+"),"+",REPT(" ",100)),COLUMNS($B:B)*100,100)+0,"No Data")

 

[Peter_SSs]

also i redo the formula of peter by this formula with new test data reference.

That formula is fine for that sample, and may be for any of your sample data. However, if the formula is trying to cope with virtually any format then I think replacing some characters with "+" and some with something else could possibly lead to problems. Rather, I would
- Replace all symbols with a space
- TRIM the result to reduce any multiple spaces to single ones
- Replace each space with 100 spaces
- etc

If your data can vary in length, then at the start, you don't know exactly how many columns to copy the formula across and it may vary from row to row so I would replace the "No Data" message with a null string "". That way you won't end up with "No Data" possibly at the right of your results.
To demonstrate what I mean, in the mini sheet below, I have used your formula in rows 1-3 and my suggested alternative in rows 5-7

23 07 13.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	400+40+40*2+500+50+50*2-(300+30+30*2)	400	40	40	2	500	50	50	2	300	30	30	2	No Data
2		No Data	No Data	No Data	No Data	No Data	No Data	No Data	No Data	No Data	No Data	No Data	No Data	No Data
3	400+20+20*2	400	20	20	2	No Data	No Data	No Data	No Data	No Data	No Data	No Data	No Data	No Data
4
5	400+40+40*2+500+50+50*2-(300+30+30*2)	400	40	40	2	500	50	50	2	300	30	30	2
6
7	400+20+20*2	400	20	20	2
Extract Numbers (2)

Cell Formulas
Range	Formula
B1:N3	B1	=IFERROR(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("+"&$A1,"*","+"),"-","+"),"("," "),")","+"),"+",REPT(" ",100)),COLUMNS($B:B)*100,100)+0,"No Data")
B5:N7	B5	=IFERROR(MID(SUBSTITUTE(" "&TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A5,"*"," "),"-"," "),"+"," "),"("," "),")"," "))," ",REPT(" ",100)),COLUMNS($B:B)*100,100)+0,"")