# Split Text to Numbers

1. ## 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.

Is there any formula for above problem?

2. ## Re: Split Text to Numbers

In B2 copied down:

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

In C2 copied down:

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

3. ## Re: Split Text to Numbers

Thank you AliGW. Your formula as well as works.

5. ## Re: Split Text to Numbers

Hi AliGW,

Do you have any idea for changing format of left number from General to Accounting just like below texts?

1,256+45
3,565,000-12
4,363,457+21
253,637+34

In this format, the second formula does not work.

6. ## Re: Split Text to Numbers

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

7. ## Re: Split Text to Numbers

I mean that something like attached spreadsheet.

8. ## Re: Split Text to Numbers

Use this in C2 copied down:

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

9. ## Re: Split Text to Numbers

Many thanks. It works now.

10. ## Re: Split Text to Numbers

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

11. ## Re: Split Text to Numbers

Yes. you are true. I took a mistake to post original sample data. I am so sorry.

12. ## Re: Split Text to Numbers

No worries - just bear it in mind in future.

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