How to check for a blank space and decide to add or not? Interesting

[zjianguk]

Hi, I have an interesting question and is useful to my case.

When a file is open, if cell A1 has a text "Hello there", then I want to make this cell add a blank space as "Hello there ". Let's save this file and close. So now if I re-open it, I want cell A1 to remove the added blank space and revert to be "Hello there". This take turns to change back and forward.

Any idea with simple Excel function, or does this have to be done with VBA?

[Pete_UK]

You couldn't do it with a formula, as a formula returns a result to the cell it is in (it can't "push" a value to another cell). Thus, A1 would have to contain the formula and you would need some other cell to indicate whether the space should be there or not (e.g. by counting the number of times the file has been saved and then determining if that is an odd or even number).

Hope this helps.

Pete

[zjianguk]

Hi Pete, it's absolutely right to have a formula in cell A1, so that the formula can make the value be dynamic with a blank space adding or not adding. Some formula in cell A1 like:

="Hello there" & xxx

Your ideas is that xxx. can be made to be the number of times the file has been saved. I'm not sure if this can be done via some Excel function, or this has to go with the VBA?

[zjianguk]

Hi Pete, it's absolutely right to have a formula in cell A1, so that the formula can make the value be dynamic with a blank space adding or not adding. Some formula in cell A1 like:

="Hello there" & xxx

Your ideas is that xxx can be made to be the number of times the file has been saved. I'm not sure if this can be done via some Excel function, or this has to go with the VBA?

[zjianguk]

Is there a way to do this? Please

[oeldere]

As already said by PeterUK.

In Excel it's only possible, if you refer to another cell.

Probably it can be done with VBA.

[zjianguk]

As already said by PeterUK.

In Excel it's only possible, if you refer to another cell.

Probably it can be done with VBA.

Hi oeldere, how to do this by referring to another cell?

[Pete_UK]

You could have a formula like this in A1:

="Hello there" & IF(MOD(X1,2)=0," ","")

where X1 would contain a count of the number of times the file was saved, and if that was an even number then a space would be added. However, a formula couldn't get that count to increment automatically when a file was saved - you would need VBA for that.

Hope this helps.

Pete

[zjianguk]

Thanks for your help, Pete. VBA must be needed