 # extracting data from a sentence-please needed ASAP

• Respected,

Needs the data to be extracted from a sentence

total is 5 data(so five formulas is needed)

-1&2 are checksums so label is not given

(1) column H

(2) column I

(3)nf% column J

(4)2A column K

(5)3A column L

Please if possible mention me some note as how this formula(logic and how to use formulas that are used in that) as I might get more of this type of problem so can solve at my end.

Zohar

• Must this be formulas? Or can you use multiple manual processes to achieve it?

Cheers, Glenn.

Beauty is in the eye of the beer-holder.

• Respected Sir,

I think it needs formulas as I have to use it further in other formulas and the automated things of excel like FILL SPACE(or something similar do not update the data when source data is changed.

Zohar

• Respected Sir,

Part of it was solved by a friend

=TRIM(RIGHT(SUBSTITUTE(LEFT(SUBSTITUTE(A3,"%",REPT(" ",255)),255),"NF‰",REPT(" ",255)),255))

for extracting nf%

but in that I cant understand logic and how to use it for extracting checksums and other data

Zohar

• For the 5 items, these are the formulas:

 =IF((SEARCH("NF‰",A2)-1)=1,"",IF(LEFT(A2,1)="║","",MID(A2,1,SEARCH("║",A2)-1))) =IF((SEARCH("NF‰",A2)-SEARCH("║",A2))=1,"",MID(A2,SEARCH("║",A2)+1,SEARCH("NF‰",A2)-SEARCH("║",A2)-1)) =TRIM(RIGHT(SUBSTITUTE(LEFT(SUBSTITUTE(A2,"%",REPT(" ",255)),255),"NF‰",REPT(" ",255)),255)) =TRIM(RIGHT(SUBSTITUTE(LEFT(SUBSTITUTE(A2,"║3A",REPT(" ",255)),255),"2A║",REPT(" ",255)),255)) =RIGHT(A2,LEN(A2)-SEARCH("║3A",A2)-2)

Cheers, Glenn.

Beauty is in the eye of the beer-holder.

• Respected Glenn Sir,

THANK YOU VERY VERY MUCH

Zohar Batterywala

• This modified formula solution similar to your post #4 formula

In H2, formula copied across right to L2 and all copied down :

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("║"&\$A2,"NF‰","║"),"%2D (2A",""),"3A",""),"║",REPT(" ",99)),COLUMN(A1)*99,99))

Regards

Bosco

• Respected Bosco_YIP,

THANK YOU VERY VERY MUCH

zohar Batterywala