• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Splitting the row data into 3 parts.

VDS

Member
Dear All,

I attach herewith the data contains some data extracted from website. It is a mixture of 3 things,

a) Company Name
b) PAN NO
3) Amount

I tried to extract the amount by IFERROR(VALUE(RIGHT(A4,(LEN(A4)-FIND(" ",A4,47)))),""), but all the rows, the starting No say 47 is not accurate due to difference in name of company. Error is coming due to that. Further, When I applied ROUND to display like 12345.00 it has no effect. How this can be corrected ?

Regarding the PAN No, the character length is fixed = 10 digits i.e, after taking the amount from the data. The remaining portion become the Company Name.

Here, data - text to columns is not working nicely as character length is not fixed.

I want to avoid custom format several times as this data has to be changed frequently.

Help is requested.


VDS
 

Attachments

  • FOR FORM26 AS - Copy.xlsx
    10.4 KB · Views: 2
Suppose amount is in Column B

Put in B4 =--RIGHT(SUBSTITUTE(A4," ",REPT(" ",LEN(A4))),LEN(A4))

Suppose PAN No is in Column C

C4= TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A4,B4,"")," .00","")," ",REPT(" ",LEN(A4))),LEN(A4)))

Suppose Company is in Column D

D4= LEFT(A4,SEARCH(C4,A4)-1)

Hope this helps,...
 
What Asheesh posted for B4 is already dynamic. So here's for C4 & D4

C4 = =TRIM(LEFT(RIGHT(SUBSTITUTE(A4," ",REPT(" ",LEN(A4))),2*LEN(A4)),LEN(A4)))

D4 = =TRIM(LEFT(A4,(LEN(A4)-LEN(TRIM(RIGHT(SUBSTITUTE(A4," ",REPT(" ",LEN(A4))),2*LEN(A4)))))))
 
Dear all,

Nice replies & thanks a lot.

One more thing. When the amount is extracted, it should be displayed to Round like 1234.00.


VDS
 
Dear all,

Nice replies & thanks a lot.

One more thing. When the amount is extracted, it should be displayed to Round like 1234.00.


VDS
Hi VDS,

Since you got extracted the amounts, you can now format it to your choice.
say:
#,##0.00

or if you want this with formula, wrap the formula with TEXT function:
=TEXT(--RIGHT(SUBSTITUTE(A4," ",REPT(" ",LEN(A4))),LEN(A4)),"#,###.00")
Regards,

P.S: it will not round, but display in 2 decimal.
 
@ Khalid,


Nice reply. Let me try and revert. Here, I would like to add a comment.


I have seen in most of the formulas the hyphen (may be minus sign ) is using after = sign and before formula. Not only here but also in various occasions depending on requirements. This concept I am not able to understand. Could u pls enlighten a little bit ?


VDS
 
@ Khalid,


Nice reply. Let me try and revert. Here, I would like to add a comment.


I have seen in most of the formulas the hyphen (may be minus sign ) is using after = sign and before formula. Not only here but also in various occasions depending on requirements. This concept I am not able to understand. Could u pls enlighten a little bit ?


VDS

Hi VDS,
In short, double hyphen sign converts TRUE/FALSE into 1/0
1 for TRUE
0 for FALSE
when formula return the array like {TRUE,TRUE,FALSE,TRUE,FALSE}
it will become {1,1,0,1,0}

For details, go through the following link and see the comments:
http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/

Regards,
 
in this case:
--RIGHT(SUBSTITUTE(A4," ",REPT(" ",LEN(A4))),LEN(A4))

you can avoid -- with:
=RIGHT(SUBSTITUTE(A4," ",REPT(" ",LEN(A4))),LEN(A4))+0
=RIGHT(SUBSTITUTE(A4," ",REPT(" ",LEN(A4))),LEN(A4))*1
=RIGHT(SUBSTITUTE(A4," ",REPT(" ",LEN(A4))),LEN(A4))^1

if you enter the formula without these operators you will see the difference, result would be in text.
Hope it clears.

Regards,
 
Back
Top