I have a FDF file that I convert using conversion software, but the values are split over several columns
I am looking for a formula to extract the jumbled values from several columns
I have shown what the values should be
It would be appreciated if someone could assist me
Try
H2=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2&D2&F2,",",""),"(","-"),")",""),CHAR(32),""))+0
copy down
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Thanks for the help
I have test a few additional numbers and it come up with #value!
Kindly check sample data and amend
May be try
H2=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CLEAN(B2&C2&D2&E2&F2&G2),",",""),"(","-"),")",""),CHAR(32),""),"■",""),".",""))/100
Always with 2 decimal places??
=SUMPRODUCT(MID(0&B2&C2&D2&E2&F2&G2,LARGE(INDEX(ISNUMBER(--MID(B2&C2&D2&E2&F2&G2,ROW($1:$50),1))*ROW($1:$50),0),ROW($1:$50))+1,1)*10^ROW($1:$50)/1000)
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Thanks Shukla
Thanks for the help Glenn
Hi Shukla
Please advise what this part of the formula means after &G2)
It will help me if I need to add to this at a later stage
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks