Split Text to Numbers

Hi everybody,

I have on column "A" below texts:

1,256+45
356,5000-12
436,000+21

There is not any space between first number, plus/minus sign and second number. I am willing to split this text to the numbers in each cell.

Please see attached excel file.

Is there any formula for above problem?

Thanks in advance for your help.

In B2 copied down:

=VALUE(LEFT(A2,IFERROR(FIND("+",A2),FIND("-",A2))-1))

In C2 copied down:

=VALUE(SUBSTITUTE(A2,B2,""))

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

It works just by changing the formatting of that column:

Excel 2016 (Windows) 32 bit

	A	B	C
1	TEXT	Left Number	Right Number
2	1436700+24	£ 1,436,700.00	24
3	76538-35	£ 76,538.00	-35
4	987120+87	£ 987,120.00	87
5	26762900-21	£ 26,762,900.00	-21

Sheet: Sheet1

Use this in C2 copied down:

=VALUE(SUBSTITUTE(SUBSTITUTE(A2,",",""),B2,""))

It worked before on the data provided originally! Next time, provide ACCURATE sample data at the outset instead of shifting the goalposts.

No worries - just bear it in mind in future.

Please try in B7 and copy down:

Formula:

Please Login or Register  to view this content.

and in D7 and copy down:

Formula:

Please Login or Register  to view this content.

@soleimani1967

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread