View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Richard
 
Posts: n/a
Default converting a string of information into excel cells

Hi,

Ah, OK, I can see a problem with E1 in that it was only returning the
first part of the name before the first and second "$" signs. I'm
assuming the name you want in this example is "Joe Dan", and I've
assumed a space between Joe and Dan. If this is what you need then
please modify E1 to be:

=SUBSTITUTE(MID(A1,LEN(D1)+LEN(C1)+LEN(B1)+4,FIND( "$",SUBSTITUTE(A1,"$","",1))+1-(LEN(D1)+LEN(C1)+LEN(B1))),"$","",1)

So I now see "JOE DAN" in E1

I couldn't see a problem with the original F1, although now having
corrected E1, there's a knock on effect on F1 which should be changed
to:

=MID(A1,FIND("^",SUBSTITUTE(SUBSTITUTE(A1,"^","",1 ),"^","",2))+2,FIND("?",SUBSTITUTE(SUBSTITUTE(A1," ^","",1),"^","",2))-1-FIND("^",SUBSTITUTE(SUBSTITUTE(A1,"^","",1),"^","" ,2)))

and I now see "123 SOMESTREET"

If it still doesn't evaluate correctly, please post back.

I suspect there may be a more elegant way of simplifying these string
functions. so I'll have another think. If you're happy with running a
VBA macro, then that would be one other option, and probably only need
a few lines of code. Let me know

Kind regards,

Richard Buttrey
Grappenhall, Cheshire, UK