Convert Text String To Numbers

[paulkc]

I have a spreadsheet containing several thousand records. I am trying to extract certain parts of a certain field. This field contains a text string that might be something like "001-018" or "003". I want to extract into 2 different columns the first number and the last number (if there is one). If column A contains the "001-018" then I want column B to have "1" and column C to have "18". If column A contains "003" then column B would have "3" and column C would be left blank. I can't figure out the correct formulas to do this. Thanks for the help.

 

[hiker95]

paulkc,

We need to see more examples of the data in column A, and the manual results in columns B and C.

What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

If you are not able to give us screenshots, see below in my Signature block: You can upload your workbook to Box Net

 

[Aladin Akyurek]

Try...

B2:

=LEFT(A2,FIND("-",A2&"-")-1)+0

C2:

=IF(ISNUMBER(FIND("-",A2)),REPLACE(A2,1,FIND("-",A2),"")+0,"")

 

[paulkc]

Aladin, that worked great except for one thing. I noticed that some of the data in column contains some strange exceptions to my previous rule. I noticed one of the cells contains something like "001--003" (notice the extra dash). Another had "001-'003". The formula in column C returns "-3" for the first instance but an error for the second instance. Would it be possible to modify the formula to account for these types of inconsistencies or would I just need to rectify these manually?

 

[Aladin Akyurek]

Aladin, that worked great except for one thing. I noticed that some of the data in column contains some strange exceptions to my previous rule. I noticed one of the cells contains something like "001--003" (notice the extra dash). Another had "001-'003". The formula in column C returns "-3" for the first instance but an error for the second instance. Would it be possible to modify the formula to account for these types of inconsistencies or would I just need to rectify these manually?

B2:

=LEFT(A2,FIND("-",A2&"-")-1)+0

C2:

=IF(ISNUMBER(FIND("-",A2)),ABS(SUBSTITUTE(REPLACE(A2,1,FIND("-",A2),""),CHAR(39),"")+0),"")

If you have more exceptions, a routine in VBA would be more appropriate to sollicite for.

 

[Haseeb Avarakkan]

Also try,

B2;

=LEFT(SUBSTITUTE(A2&"-","-",REPT(" ",50)),50)+0

C2;

=IF(ISNUMBER(FIND("-",A2)),SUBSTITUTE(RIGHT(SUBSTITUTE(A2,"-",REPT(" ",50)),50),CHAR(39),"")+0,"")

 

[paulkc]

Is there some way to do this so that is separates the numbers regardless of what is in between? I have run into a lot of inconsistencies in these numbers. Everything from the original 001-003 to 1~3 or 1/.3, etc. Any suggestions?

 

[hiker95]

paulkc,

To solve your request correctly, we need to see your raw data, and we need to see the results manually entered by you in columns B and C.

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

If you are not able to give us screenshots, see below in my Signature block: You can upload your workbook to Box Net

 

[paulkc]

Ok, here is a sample of the data. I cannot guarantee that this is all the possible characters because as you can see there is no rhyme or reason to the way the range was indicated. I am cleaning up quite a few of these and then importing them into an Access Database.

Excel Workbook
	C	D	E
1	10~	10
2	12~	12
3	17~27	17	27
4	31/`34	31	34
5	1_5	1	5
6	12	12
7	001-003	1	3
8	003-012	3	12
Sheet1

Excel 2010

 

[jasonb75]

This is not an ideal solution, it is slow to calculate, and volatile, but it would give you a "standard" format that you could maybe copy / pastespecial, then delimit with text to columns using "space" as delimiter.

Note that MCONCAT requires the MOREFUNC add-in.

Excel Workbook
	A	B
4	10~	10
5	12~	12
6	17~27	17 27
7	31/`34	31 34
8	1_5	1 5
9	12	12
10	001-003	001 003
11	003-012	003 012
Sheet