Insert a "|" into every cell
 

I have a huge excel spreadsheet and i need to insert a | symbol at the
start of every cell. Obviously i dont want to do this manually and it'd
be nice to edit in some way so that this | symbol is automatically
inserted.

Does anyone have any ideas how this can be done?

Thanks for any help,

Lewis.


--
doof205
------------------------------------------------------------------------
doof205's Profile: http://www.excelforum.com/member.php...o&userid=28058
View this thread: http://www.excelforum.com/showthread...hreadid=475752

Select the range, type the symbol, then press CTRL+ENTER
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"doof205" wrote in
message ...

I have a huge excel spreadsheet and i need to insert a | symbol at the
start of every cell. Obviously i dont want to do this manually and it'd
be nice to edit in some way so that this | symbol is automatically
inserted.

Does anyone have any ideas how this can be done?

Thanks for any help,

Lewis.


--
doof205
------------------------------------------------------------------------
doof205's Profile:
http://www.excelforum.com/member.php...o&userid=28058
View this thread: http://www.excelforum.com/showthread...hreadid=475752

Select the range, type the symbol, then press CTRL+ENTER

should read better as:

Select the range,
click inside the formula bar and type the symbol,
then press CTRL+ENTER
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Sorry, i should have explained it more clearly. I actually want to keep
the data that is in the cells but insert a | before any existing data.

eg.

data - |data
more - |more

I hope this can be done, and thanks for the reply.

Lewis.


--
doof205
------------------------------------------------------------------------
doof205's Profile: http://www.excelforum.com/member.php...o&userid=28058
View this thread: http://www.excelforum.com/showthread...hreadid=475752

Hi

Then in another sheet enter in A1 of Sheet2
="|"&Sheet1!A1
Copy down and across as required.
When complete, press Ctrl-A to mark the whole sheet, Ctrl-C to copy and
with the cursor in cell A1, Paste SpecialValues

Regards

Roger Govier



doof205 wrote:

Sorry, i should have explained it more clearly. I actually want to keep
the data that is in the cells but insert a | before any existing data.

eg.

data - |data
more - |more

I hope this can be done, and thanks for the reply.

Lewis.

In the column adjacent to the data (let us say data are in column A:A),
use the following formula in the first cell (B1 in here)
="|"&A1

HTH
Kostis Vezerides

Roger, I tried this VBA solution but get R/T 424 on line 4 (Object
required)..
I don't understand (this type thing happens all too often - as obviously I
haven't begun to understand this mysterious
"Excel Object Model" beast. grrrrrrrrr

Sub Test()
Rng = Selection
For Each C In Rng
C.Value = "|" & C.Value
Next C
End Sub


"Roger Govier" wrote in message
...
Hi

Then in another sheet enter in A1 of Sheet2
="|"&Sheet1!A1
Copy down and across as required.
When complete, press Ctrl-A to mark the whole sheet, Ctrl-C to copy and
with the cursor in cell A1, Paste SpecialValues

Regards

Roger Govier



doof205 wrote:

Sorry, i should have explained it more clearly. I actually want to keep
the data that is in the cells but insert a | before any existing data.

eg.

data - |data
more - |more

I hope this can be done, and thanks for the reply.

Lewis.

Since Rng is an object -- not a simple variable like an integer or string, you
have to use Set:

Option Explicit
Sub Test()
Dim Rng As Range
Dim C As Range
Set Rng = Selection
For Each C In Rng
C.Value = "|" & C.Value
Next C
End Sub

(And better to declare your variables, too.)

Jim May wrote:

Roger, I tried this VBA solution but get R/T 424 on line 4 (Object
required)..
I don't understand (this type thing happens all too often - as obviously I
haven't begun to understand this mysterious
"Excel Object Model" beast. grrrrrrrrr

Sub Test()
Rng = Selection
For Each C In Rng
C.Value = "|" & C.Value
Next C
End Sub

"Roger Govier" wrote in message
...
Hi

Then in another sheet enter in A1 of Sheet2
="|"&Sheet1!A1
Copy down and across as required.
When complete, press Ctrl-A to mark the whole sheet, Ctrl-C to copy and
with the cursor in cell A1, Paste SpecialValues

Regards

Roger Govier



doof205 wrote:

Sorry, i should have explained it more clearly. I actually want to keep
the data that is in the cells but insert a | before any existing data.

eg.

data - |data
more - |more

I hope this can be done, and thanks for the reply.

Lewis.




--

Dave Peterson

Hi Jim,

I too am pretty novice at this VBA lark, but I think you have declared Rng
as a variable of a Selection object. I don't think Selection exists as a VBA
object(?)

Ewan.

"Jim May" wrote:

Roger, I tried this VBA solution but get R/T 424 on line 4 (Object
required)..
I don't understand (this type thing happens all too often - as obviously I
haven't begun to understand this mysterious
"Excel Object Model" beast. grrrrrrrrr

Sub Test()
Rng = Selection
For Each C In Rng
C.Value = "|" & C.Value
Next C
End Sub


"Roger Govier" wrote in message
...
Hi

Then in another sheet enter in A1 of Sheet2
="|"&Sheet1!A1
Copy down and across as required.
When complete, press Ctrl-A to mark the whole sheet, Ctrl-C to copy and
with the cursor in cell A1, Paste SpecialValues

Regards

Roger Govier



doof205 wrote:

Sorry, i should have explained it more clearly. I actually want to keep
the data that is in the cells but insert a | before any existing data.

eg.

data - |data
more - |more

I hope this can be done, and thanks for the reply.

Lewis.

Well. i didn't understand half of this thread but what i did understand
helped me to stick the |'s in.

Thanks for your replies.


--
doof205
------------------------------------------------------------------------
doof205's Profile: http://www.excelforum.com/member.php...o&userid=28058
View this thread: http://www.excelforum.com/showthread...hreadid=475752

Dave:
Thanks for the explanation; I think I actually understand something now,
that I didn't before!! Halleluhah!!!
Jim


"Dave Peterson" wrote:

Since Rng is an object -- not a simple variable like an integer or string, you
have to use Set:

Option Explicit
Sub Test()
Dim Rng As Range
Dim C As Range
Set Rng = Selection
For Each C In Rng
C.Value = "|" & C.Value
Next C
End Sub

(And better to declare your variables, too.)

Jim May wrote:

Roger, I tried this VBA solution but get R/T 424 on line 4 (Object
required)..
I don't understand (this type thing happens all too often - as obviously I
haven't begun to understand this mysterious
"Excel Object Model" beast. grrrrrrrrr

Sub Test()
Rng = Selection
For Each C In Rng
C.Value = "|" & C.Value
Next C
End Sub

"Roger Govier" wrote in message
...
Hi

Then in another sheet enter in A1 of Sheet2
="|"&Sheet1!A1
Copy down and across as required.
When complete, press Ctrl-A to mark the whole sheet, Ctrl-C to copy and
with the cursor in cell A1, Paste SpecialValues

Regards

Roger Govier



doof205 wrote:

Sorry, i should have explained it more clearly. I actually want to keep
the data that is in the cells but insert a | before any existing data.

eg.

data - |data
more - |more

I hope this can be done, and thanks for the reply.

Lewis.




--

Dave Peterson