Force a Tab in a formula w/o VBA(more help needed!!!!!)

[m540165]

Is there a way other than using vba to force a tab.
I have a string I would like to import into a cell and then force the latter part of the data into the next cell.
The string would be "Qtr 4" "ACT2001".
So "Qtr 4" be in the first cell and "ACT2001" in the next cell.
What would be the formula to do that?

I want the user to have to do as little as possible.

Moving an Essbase substitution variable into an excel spreadsheet and want to use code split variables into 2 different cells.

Any help would be appreciated!!

_________________
Paul Muller

[ This Message was edited by: m540165 on 2002-05-17 19:55 ]

[PHMayfield]

The "text to Columns" item under the data menu should work. choose a space for the delimiter.

[m540165]

On 2002-05-17 14:30, m540165 wrote:
Is there a way other than using vba to force a tab.
I have a string I would like to import into a cell and then force the latter part of the data into the next cell.
The string would be "Qtr 4" "ACT2001".
So "Qtr 4" be in the first cell and "ACT2001" in the next cell.
What would be the formula to do that?

Any help would be appreciated!!

[Tom Urtis]

Can you please give some more information:

(1) What do you mean by "force a tab"?
(2) Will the text string always be in the syntax of that single example you give?
(3) This appears do-able using formulas or other Excel tricks but VBA might be the better way to go, since you want your users to do as little as possible
(4) Follow up to (3) above, how many entries at a time do you have? If there are thousands, a formula for each entry would bog down the file, and that wouldn't be much fun either.

So, whatcha think? VBA totally out of the question? If so, we can provide an answer, I'm just looking out for the best solution.

[PDuPre']

On 2002-05-17 14:30, m540165 (Paul Muller) wrote:
I have a string I would like to import into a cell and then force the latter part of the data into the next cell.

How are you importing the string? Is it in a .txt file?
Philip

[m540165]

To answer Phillips question:
Through the Key board.

[PDuPre']

On 2002-05-20 09:08, m540165 wrote:
To answer Phillips question:
Through the Key board.

OK. Let me see if I have this straight.
You have a cell that contains the string between []: ["Qtr 4" "ACT2001"].
You want to have [Qtr 4] in one cell and [ACT2001] in another cell.
----------------------
Since you are using EssBase have the first cell that contains the first string located on another worksheet. We put it on the Instructions worksheet along with the instructions for the workbook. For this example, let's say it is Instructions, cell A1.
The following formulas will seperate the input.

In the cell you want [Qtr 4] to appear, type the formula =MID(Instructions!A1,2,FIND(""" """,Instructions!A1)-2)

In the cell you want [ACT2001] to appear, type the formula =MID(Instructions!A1,FIND(""" """,Instructions!A1)+3,(LEN(Instructions!A1)-(FIND(""" """,Instructions!A1)+3)))

-----------------
If, for some reason you want the quotes to still appear around the variable then follow the following instructions:

In the cell you want ["Qtr 4"] to appear, type the formula =MID(Instructions!A1,1,FIND(""" """,Instructions!A1))

In the cell you want ["ACT2001"] to appear, type the formula =MID(Instructions!A1,FIND(""" """,Instructions!A1)+2,(LEN(Instructions!A1)-(FIND(""" """,Instructions!A1)+1)))

Regards,

Philip