Excel Questions

Forums › Off topic › Excel Questions

1 | 2

networkn

Networkn
32864 posts

Uber Geek
+1 received by user: 15454

ID Verified

Trusted

Lifetime subscriber

#629418 23-May-2012 11:51

Unfortunately I can't really script well enough in VB. To tidy it up I'm going to put the new concatenated fields on a new sheet!

floydbloke

3646 posts

Uber Geek
+1 received by user: 4554

ID Verified

#629421 23-May-2012 12:02

networkn: Hmm reformatting this spreadsheet is no small task! I also need to add the word user to every cell in a column now too, could someone show me how to do that please? It's a cell with numbers and I just need a space and then "user" I am thinking it will need to be something like =a1+ " User" or something? I wish it was possible to modify the existing cells rather then adding a new column of cells

Is all your data in a single column?
You do know that you can grab the bottom-right corner of a cell and drag it down to copy it all the way down the column, and Excel is smart enough to insert the right column reference wehn you're copying.

So if your current data is in column A, starting on row 1, I would do as follows:
* insert a column before B if you need to
*in B1, put the formula =A1&" User"
*grab the bottom-right corner of B1 and drag it all the way down to the bottom of your data
*delete column A if you want to

Sometimes I use big words I don't always fully understand in an effort to make myself sound more photosynthesis.

networkn

Networkn
32864 posts

Uber Geek
+1 received by user: 15454

ID Verified

Trusted

Lifetime subscriber

#629422 23-May-2012 12:04

Thanks, yup I know I can use that drag icon :)

I'll have a play with it when I get a chance..

NzBeagle

971 posts

Ultimate Geek
+1 received by user: 128

Trusted

#629423 23-May-2012 12:06

networkn: Unfortunately I can't really script well enough in VB. To tidy it up I'm going to put the new concatenated fields on a new sheet!

Tested in that if I have a column of numbers, it will convert that column to "User #"

Sub Networkn()
Dim a, b, c As String
Range("A1").Select
While ActiveCell <> Empty
a = "User "
b = ActiveCell.Text
c = a + b
ActiveCell = c
ActiveCell.Offset(1, 0).Select
Wend
End Sub

networkn

Networkn
32864 posts

Uber Geek
+1 received by user: 15454

ID Verified

Trusted

Lifetime subscriber

#629426 23-May-2012 12:09

Wow thank you for doing that for me, I feel a little helpless :) How do I implement it? I presume I don't just paste it into a cell?

NzBeagle

971 posts

Ultimate Geek
+1 received by user: 128

Trusted

#629427 23-May-2012 12:10

networkn: Wow thank you for doing that for me, I feel a little helpless :) How do I implement it? I presume I don't just paste it into a cell?

In Excel you'll have to open the Editor, Alt + F11 brings it up, and then add a new module

Edit: Changes made using VBA can't be undone, so test it out, or don't save and reopen.

Geekzone

Want to support Geekzone and browse the site without the ads? Subscribe to Geekzone now (monthly, annual and lifetime options).

bazzer

3438 posts

Uber Geek
+1 received by user: 267

Trusted

#629433 23-May-2012 12:35

Honestly, this seems like overkill. floydbloke's solution will work but you'll need to copy/paste special values before deleting A since it is referenced in the formula.

I'm assuming you just want to replace them all once and then you're done?