Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 -- |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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. |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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. |
#10
|
|||
|
|||
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 |
#11
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Update of cell values after insert row? | Excel Worksheet Functions | |||
Insert a number of rows based on a value in a cell on active row | Excel Discussion (Misc queries) | |||
Cell color based upon cell value | Excel Discussion (Misc queries) | |||
cell color index comparison | New Users to Excel | |||
Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions |