delimit

[PE]

Hello,

I am facing an issue with delimiting a numeric value in excel. The excel sheet i have is the exported file from SQL database. I do not have access to the database. The value I want to delimit is like 2,455,1,666. I want to have this value in four subsequent cells. I tried using LEN and other functions to first calculate that value will be delimited to how many cells. For example =(ROUND(LEN(A1)/3,0))+(LEN(A1)-(ROUND(LEN(A1)/3,0)*3)). The first issue here is LEN function stops working or give erroneous results if the value is more than 16 digits. Then the next challenge is to delimit in 4 cells.

Please help if anyone has any idea.

Regards,
PE

 

[Peter_SSs]

Could you just use Text To Columns with a comma as the delimiter?

What result do you want for that given sample?

What Excel version are you using?

 

[PE]

Hello Peter,

Text to column doesn't work here because cell value is in number format and therefore excel doesn't read comma " , ".

Value: 2,346,123 should be delimited to 2 then 346 then 123, all three values in three different cells

I am using excel 2007.

Many thank for your attention to this issue.

Regards,
Subodh

 

[Peter_SSs]

Text to column doesn't work here because cell value is in number format and therefore excel doesn't read comma " , ".

Value: 2,346,123 should be delimited to 2 then 346 then 123, all three values in three different cells

How does that relate to the example you gave earlier? It doesn't appear to be a number. How should it be delimited?

The value I want to delimit is like 2,455,1,666.

 

[aniketbhanu]

As peter suggested earlier, it can be easily done using text to columns (Data -> text to columns) in excel 2007)

But if you still want to do it using formulae for some reason, try the following.

I assume the string is in column A.

Cell____Formula___________________________ Value
A1 ____- ________________________________7,235,65,2
B1____ =LEFT(A1,SEARCH(",",A1,1)-1)________ 7
C1____ =RIGHT(A1,LEN(A1)-SEARCH(",",A1,1)) _235,65,2
D1____ =LEFT(A1,SEARCH(",",A1,1)-1)________235
E1____ =RIGHT(A1,LEN(A1)-SEARCH(",",A1,1)) _65,2
F1 ____=LEFT(A1,SEARCH(",",A1,1)-1) ________65
G1 ____=RIGHT(A1,LEN(A1)-SEARCH(",",A1,1))__ 2

Columns B,D,F,G will have the values you want

Hope that helps

(Have used __s for indentation. How to indent posts?)