Excel Questions

Forums › Off topic › Excel Questions

1 | 2

networkn

Networkn
32357 posts

Uber Geek

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

3523 posts

Uber Geek

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

Did Eric Clapton really think she looked wonderful...or was it after the 15th outfit she tried on and he just wanted to get to the party and get a drink?

networkn

Networkn
32357 posts

Uber Geek

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

962 posts

Ultimate Geek

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
32357 posts

Uber Geek

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

962 posts

Ultimate Geek

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.

bazzer

3438 posts

Uber Geek

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?

Quic Broadband

Move to New Zealand's best fibre broadband service (affiliate link). Free setup code: R587125ERQ6VE. Note that to use Quic Broadband you must be comfortable with configuring your own router.

networkn

Networkn
32357 posts

Uber Geek

ID Verified

Trusted

Lifetime subscriber

#629435 23-May-2012 12:37

Yes one off replacement and then we are done.

floydbloke

3523 posts

Uber Geek

ID Verified

#629437 23-May-2012 13:00

bazzer: ... but you'll need to copy/paste special values before deleting A since it is referenced in the formula....

Good spotting of my deliberate omission there Bazzer. Just making sure everyone is paying attention.

Did Eric Clapton really think she looked wonderful...or was it after the 15th outfit she tried on and he just wanted to get to the party and get a drink?

networkn

Networkn
32357 posts

Uber Geek

ID Verified

Trusted

Lifetime subscriber

#629438 23-May-2012 13:02

Yup I picked that up when I was looking at the recommendation thank you.

1 | 2