using macro to reorder and combine

kellem80

Board Regular
Joined
Apr 2, 2008
Messages
95
I'd like to create a macro that will take the information from the first two columns reformat and create the third column as shown formatted. Any help would be appreciated.
<TABLE style="WIDTH: 180pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=240 x:str><COLGROUP><COL style="WIDTH: 54pt; mso-width-source: userset; mso-width-alt: 2633" width=72><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3657" width=100><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 54pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 width=72>Date</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 51pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=68>Name</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=100>Number</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=17 x:num="40425.384027777778">9/4/10 9:13</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26>Bob Smith</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>100904-0913BS</TD></TR></TBODY></TABLE>
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,881
Hi kellem80
Welcome to the board

Try in C2:

=TEXT(A2,"yymmdd-hh:mm")&LEFT(B2)&MID(B2,1+FIND(" ",B2),1)
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

You can do this with a simple Excel formula (no macros necessary).
Assuming that your values are in A2 and B2, column A is formatted as date/time, and you just have your values wrapping and not using soft carriage returns, this formula would work:

=TEXT(A2,"yymmdd-hhmm")&LEFT(B2,1)&MID(B2,FIND(" ",B2)+1,1)
 

kellem80

Board Regular
Joined
Apr 2, 2008
Messages
95
Thanks, works great but one more question, what if column A is the formula =now() but I want column C static? I think this is why I believed I needed a macro......
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Very similar in VBA:

Range("C2") = Format(Range("A2"), "yyyymmdd-hhmm") & Left(Range("B2"), 1) & Mid(Range("b2"), InStr(Range("B2"), " ") + 1, 1)

Or just use the earlier formulas we provided and use Copy -> Paste Special -> Values to convert those formulas to hard-coded values (can use Macro Recorder to get VBA code for that).
 

kellem80

Board Regular
Joined
Apr 2, 2008
Messages
95
Very similar in VBA:



Or just use the earlier formulas we provided and use Copy -> Paste Special -> Values to convert those formulas to hard-coded values (can use Macro Recorder to get VBA code for that).

OK, completely new to VBA, so bear with me---got nothing from what you provided me. All this is for a form, so I want the user to only enter their name, the date will be auto fill from the =now(), then the formula will creat a unique code for the entry combining date and name information; so the copy paste special, not an option.
Tried the recorder and didn't really get what I think I want. It would be best if the conversion happened on save (so when they save the sheet it turns the date and time to a static date and time)



Most of my experience with macros has been editing ones previously set up for me, so please be kind, and bear with me.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

It is really quite frustrating when you keep giving us a little bit of pertinent information with each succeeding post instead of asking the full, real question up front. Your question now is much different than your original post.
 

kellem80

Board Regular
Joined
Apr 2, 2008
Messages
95
It is really quite frustrating when you keep giving us a little bit of pertinent information with each succeeding post instead of asking the full, real question up front. Your question now is much different than your original post.

No need to be so rude, really, you're a moderator? I didn't know about the date formula, which changed my original post, until after I posted and tested the reponses. So yes, it was different, but I DID know that starting a new thread is discouraged, so I continued with my original thread and expanded----as I have seen numerous folks do without being "put in their place" so to speak.

Wow, what a welcome to the forum.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
I know I am a Moderator, I am merely stating facts, much like you were when you stated that you "got nothing from what you provided me". Where is the rude part?

We are simply trying to help you, but it is very tough when the question keeps changing. Twice now we thought that we had answered the question, only to find out that what you had asked really isn't exactly (or completely) what you are looking for.

Had I known the extent of your question from the beginning, I probably would not have replied, as I work very little with Excel forms myself, and would have left it to someone with more expertise in that area. So the dangers in asking general or vague questions up front, is that you might not be attracting the right people to deal with your real, more detailed issue.

I realize that you are a new member here, and am trying to guide you in how to use this board to get the most out of it that you can.

The answers that are provided can only be as good as the questions that are asked. So the key to getting a good answer that works for you is to ask a good, complete questions that explains exactly what it is you are trying to do.

If you are working with an existing form that already has VBA code behind it, it would probably be best to post that here too. I am thinking it probably makes more sense to do this calculation at the time the data is entered through the form, rather than upon saving the worksheet. Would you agree?
 

kellem80

Board Regular
Joined
Apr 2, 2008
Messages
95
Form is still in worksheet, no VBA whatsoever. The UID I'm trying to create through this combination of Date/time & initials would be the first piece at this point.
To address your question, it doesn't matter if it is upon save or upon entering, but what then would prompt it changing the UID to static (if not "save"), since upon open the date/time will populate automatically, are you thinking it would it be the entering of the Name?



btw, my comment would have better read, "I tried what you provided but got nothing back from excel as a result", meaning, that I entered what you provided me both in the VB editor and just trying in the cell itself and nothing happened either way. Not sure I was entering your logic correctly.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,529
Messages
5,596,695
Members
414,088
Latest member
rodriboraun

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top