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.
Select the range, type the symbol, then press CTRL+ENTER
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"doof205" <[email protected]> wrote in
message news:[email protected]...
>
> 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.
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
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 Special>Values
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.
>
>
>
>
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" <[email protected]> wrote in message
news:[email protected]...
> 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 Special>Values
>
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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 Special>Values
> >
> > 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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 Special>Values
> >
> > 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.
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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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 Special>Values
> > >
> > > 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
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks