Need formula

[maddy29]

Hi Chandoo,

I had a question. I have column where i put remarks as
4-days Late,1.5-Absent, & 4-Half Day. I want to know is there any way we can get all in 3 different column through a formula. I applied =IFERROR(MID(Z3,FIND("-Late/Absent/Half Day",Z3)-1,1),"")like that.

Please help me to understand.

thanks
Madhoor

 

[bobhc]

Have you tried text to columns

 

[NARAYANK991]

Hi Madhoor ,

Will there be only these 3 different categories , or can there be more ?

What will the headers of these columns be ? Will they be the same as the text of these categories ?

Narayan

 

[bosco_yip]

Split numeric value only, formula way :

1] A1, enter : 4-days Late,1.5-Absent, & 4-Half Day

2] B1, formula copy across :

=TRIM(MID(SUBSTITUTE(" "&TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1,"-days Late,"," "),"-Absent, &"," "),"-Half Day"," "))," ",REPT(" ",50)),COLUMNS($A1:A1)*50,50))

3] Result in B1, C1 and D1 returned 4, 1.5 and 4.

Regards
Bosco

 

[maddy29]

Hello Bosco ,

Yes I did... but remarks are separated by &,-,comma etc...field is manual so users can write anything there. when I tried text to column it didnt work out that way..

any suggestion to make it better...

thanks

 

[maddy29]

Hi Madhoor ,

Will there be only these 3 different categories , or can there be more ?

What will the headers of these columns be ? Will they be the same as the text of these categories ?

Narayan

Hi Narayan,

it depends on users...it could br more depending on situation.

 

[maddy29]

Hi Bosco,

tried the same.... didnt work out...I have uploaded the file for your reference.

 

[NARAYANK991]

Hi ,

There are almost a dozen different types of text that can accompany the numbers ; a formula which will take care of all of these and be absolutely fool-proof is either going to be a monster or an impossibility , given the variation ( such as spelling mistakes ) that can come into free text data entry.

It is essential that you set up a proper data entry mechanism , such as a userform , which will ensure that users do not have the luxury of entering what ever they feel like.

Data validation done at the time of data entry makes data processing that much easier and the results will be more reliable.

At present , someone may be able to give you a formula , but can anyone assure you that the results will be correct over a thousand rows of data ? There will be no way of proving that the formula has taken care of every number present in the input. When dealing with such data , you need to have a system by which the data entry itself can be verified , and also the processed data can be verified , by some kind of employee totals , overall total , checksum ,...

Narayan

 

[maddy29]

Hi ,

There are almost a dozen different types of text that can accompany the numbers ; a formula which will take care of all of these and be absolutely fool-proof is either going to be a monster or an impossibility , given the variation ( such as spelling mistakes ) that can come into free text data entry.

It is essential that you set up a proper data entry mechanism , such as a userform , which will ensure that users do not have the luxury of entering what ever they feel like.

Data validation done at the time of data entry makes data processing that much easier and the results will be more reliable.

At present , someone may be able to give you a formula , but can anyone assure you that the results will be correct over a thousand rows of data ? There will be no way of proving that the formula has taken care of every number present in the input. When dealing with such data , you need to have a system by which the data entry itself can be verified , and also the processed data can be verified , by some kind of employee totals , overall total , checksum ,...

Narayan

Hello Narayan,

I know thats true... but I cant change for now.... though I used iferror(mid.......
formula and got the results in different designed columns but it took hell lot of time... will keep looking to find any better way