INDIRECT function - need help

Originally Posted by dansparts

Still trying to understand.
If I was pasting my text into A2, instead of A3, and I then wanted the value from A3 (not as currently A4) to go to C3, what would change in the formula? Just the $A$4 to $A$3?

Not very clear for me, you just to give a trial to see what happened and post it again.

Originally Posted by dansparts

Is there any easy way to do that, and account for variability in number of characters in strings 2 & 3?

Assuming string is in A1
In B1:

=TRIM(LEFT(SUBSTITUTE($A1," ",REPT(" ",100)),100))

In C1:

=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",100)),100,100))

In D1:

=TRIM(RIGHT(SUBSTITUTE($A1," ",REPT(" ",100)),100))

Sharing: Replace any space in string with 100 spaces, then use LEFT(...,100), MID( take 100 chars start from 100) and RIGHT(...,100)

Originally Posted by bebo021999

In B1:

=TRIM(LEFT(SUBSTITUTE($A1," ",REPT(" ",100)),100))

Note: formula +0 to get real date instead of text

Originally Posted by dansparts

Neither of those formulas seems to work correctly.
The 2nd one got rid of the leading zero in sub-string #2, but re-attached sub-string #3.

Have you tried to add zero to that MID formula?

=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",100)),100,100))+0