Add Single Character Before & After Each Cell Value In Column
Jan 27, 2009
I am trying to do is put an asterisk at the beginning and the end of the Information in a cell for the entire column. Is there a way to make a macro that, once I highlight a column, it will put an asterisk at the beginning and end of the information in each cell in that column until a blank cell is reached where the macro will then stop? This will save me a lot of work. It will work for me if a particular column has to be defined in the macro, but would be better if it is one I can just highlight.
This would also work fine for me if I just had to highlight the cells that I want to use as well. Sorry if this seems to be a stupid question, but I am a very novice excel user
View 7 Replies
ADVERTISEMENT
Nov 27, 2006
I would like to enter an X or O into a square cell so that it completely fills the cell.
I want to eliminate the space between the text character and the cell border.
The character does not necessarily have to be text so long as it can be automatically entered into the cell by a macro and can also be deleted by a sub routine to refresh the worksheet for new data.
I created autoshapes for a cross and a circle but I have no idea how to substitute these for the text characters in the worksheet or delete them afterwards.
View 9 Replies
View Related
Mar 1, 2012
I have a string of text in cell A2. In cell B2 of my spreadsheet is a formula that calculates a number based on the text string in cell A2.
I want to write a VBA loop that removes a single character from the cell A2 string, then calculate the new value in cell B2. I want this loop to continue until the value in B2 falls below a set value (in this case 60).
My code so far
Sub trim_text()
Dim mytext As String
Dim myanswer As Integer
mytext = Range("A2")
myanswer = Range("B2")
Do While myanswer > 60
mytext = (Right(mytext, Len(mytext) - 1))
Loop
End Sub
This obviously does not work. In my excel table I have a formula in cell B2 to calculate "myanswer" will this work, or does that code have to be placed into the VBA code?
View 3 Replies
View Related
Dec 2, 2013
i have a device that produce its data as following:
NTFLog_D2013-02-12_T104016.csv
I need to divide the single column to 7 column as separated by semicolon ";"
View 2 Replies
View Related
Mar 21, 2008
I have a text file containing internet explorer browser history. The file has data in the following format (in Excel all data is in 1 column): ...
View 9 Replies
View Related
Apr 13, 2013
have to deal with a text database into Excel but ended up finding irregular format that need to be fixed. As it can be seen within the text right below, there are space delimiters in between the words. I want to replace always the 2nd space from right to left with a semicolon.
20/10/2012 CENTAURO CE 39 (06/10) 57.97 0.00
20/10/2012 CENTAURO CEFT 534 (09/10) 1,235.34 0.56
20/10/2012 CENTAURO (06/10) 5,345,200.00 45.00 1.01
20/10/2012 TFRE (06/10) 1.00 0.00
View 5 Replies
View Related
Oct 5, 2007
When I try to add the Richtextbox control, I get this error: "The control could not be created because it is not properly licensed." Since I'm in a corporate setting, I don't think I can get around this (since I don't have access to the computer's registry and several users will be using it). I have figured out a way to present the user with some rich text controls (Bold, Italics, Underline, Font, Color, etc), which will add special characters around the selected text which then is interpreted by a procedure and formats the text in the output destination (HTML document).
However, that tends to clutter up the textbox and makes it somewhat cumbersome for some users to edit the text. I'd prefer to have them see the formatted text in the textbox, if possible. So, is there a way to get some sort of Rich Text Box without the RichTextBox control? It's long shot, I know.
View 3 Replies
View Related
May 1, 2008
I need a formula that counts the number of cells in a range that only contains one character. Example, column A contains:
Hello
Goodbye
A
B
Hello
Goodbye
In the above, I want the result to be 2. I've tried =countif(a1:a6,len=1) but that doesn't work.
View 2 Replies
View Related
Jul 13, 2012
There is a method to use ROW(1:10) within an array formula to strip a string into individual elements e.g.
HELLO > {"H","E","L","L","O"}
I cannot find it anywhere, thought it was something like =MID(A1,ROW(1:10),1) but not yielding results.
View 4 Replies
View Related
May 20, 2008
I have two excel sheets exported from two sources of information. Both sheets contain 1 column which is the primary data, ie, there is no duplicate of that information. Each information in these column represent 1 device in the real world.
Now, for example:
In one sheet, I have: SDV0620B
In the other sheet, I have: SSDV-B0620B
Please note that, both data actually represent the same device. What I now need to make sure is that, I ccan identify each corresponding data from the two sheets.
So, here is the problem that I am facing, identifying two data which are same but has two different representation techniques. (since the data are from two different sources)
I came up with the idea that, if I can check the sequence of characters with one column to another, then I might be able to pull it off.
For example,
we take SDV0620B, and match the sequence of character with SSDV-B0620B.
therefore, first is S (from SDV0620B)
match with SSDV-B0620B
then, D
match SSDV-B0620B
then V
match SSDV-B0620B
and so on, until the end.
And if the 1st sequence is found in the second sequnce of characters, then it is called a match! Hope I have explained it clearly. I have provided a file with some of the data copy and pasted out, to give you an idea of what the values look like.
View 9 Replies
View Related
Jan 23, 2014
Following the tips doesn't allow to remove a character.
I exported email addresses from Outlook and they have the following character ' in front of the email address.
Using Excel 2007.. it says :"check if your search formatting and criteria are defined correctly. If you are sure that matching data exists in this workbook, it may be on a protected worksheet. Excel cannot replace data on a protected worksheet"
So I made a new file, and copied and pasted the cells into the new spreadsheet.. same message.
View 4 Replies
View Related
Jul 7, 2009
I'm creating an excel spreadsheet that will be sent out to a few users to enter data into. In this spreadsheet, multiple columns contain drop down lists created using the Data Validation tool. The lists are referencing named ranges on a second worksheet.
In these drop down lists are terms that consists of one or two words. What I'm trying to do is when a user goes to select an option from the drop down list, I want the matching one letter code to be entered into the cell instead. For example,
Column AK has drop down list with following options:
Good
Moderate
Poor
When a user selects one of the options, I want one the following respective letter to appear instead......
View 2 Replies
View Related
Feb 17, 2014
I have a column of numbers that I need to reformat. I created a macro to copy/paste special/transpose to get the data into separate columns. I then used & to get them into one cell. I can only have five sets of # in each cell. The macro works but it replaces the previous set of accounts. Is this the best way to reformat these numbers? If so, how do I correct the macro so that it doesn't replace the data i already reformatted?
I have attached a sample spreadsheet.
View 3 Replies
View Related
Jul 15, 2014
I have a worksheet that I use to store several static lists which populate various combo boxes in my application. However, I'd also like to use this worksheet to store a single column of data which can shrink or grow dynamically.
I have no problem with finding the last cell and adding data to that cell, but I'm having trouble with how to go about deleting the value of a single cell.
So as an example, If I've populated Cells A1:A10, and I want to delete the value that's in A6, how do I go about moving cells A7:A10 up one to fill in any empty spaces?
View 8 Replies
View Related
May 10, 2012
I have a column of data that will vary in length and I'm trying to write a macro to combine it all into one cell. Basically I need to do the equivalent of entering "=A1&A2&A3..." for the entire column. I started by creating an array string variable and have the data in grp() but now I'm stuck trying to figure out some sort of loop to keep adding onto the same cell or something
View 6 Replies
View Related
Jan 11, 2007
I'd like to reference Column A to a single cell in a different sheet.
=StoresServiced!A2
Returns the value of A2 in the sheet called StoresServiced to another sheet.
What I want to do is, the values from A2 to A102 to show in one cell, separated by commas. So if theres value in A2 (8009) and A3 (8010), I want the active cell (ex. M43) to look like this -> 8009,8010 -- so on and so fort
View 9 Replies
View Related
Sep 20, 2009
I have a cell which will contain SER01+SER02+SER03
and what i need it to contain is [SER01]+[SER02]+[SER03]
and shocker is i've got this to work for the first instance but not the other two
code as below... be grateful for your help
Sub measure1()
Dim list As String, pos As Integer, refl As String, refr As String, newlist As String
list = Cells(1472, 16).Value
pos = InStr(list, "+")
refl = Left(list, pos - 1)
refr = Right(list, pos + 1)
newlist = "[" & refl & "]"
Cells(1472, 17) = newlist
End Sub
View 9 Replies
View Related
Nov 28, 2012
Question is this, "can a single cell in a column be divided into two parts?"
View 14 Replies
View Related
Mar 30, 2013
How can I identify the last cell of a single column range . . . i.e. the last cell address of range C1..C15 is C15. The range will have a name of "reg".
View 9 Replies
View Related
Mar 25, 2008
I'm trying to separate a cell containing:
TSB w/6.5% NaCl - NS 29002TSB w/6.5% NaCl - NS 29003TSB w/6.5% NaCl - NS 29004
into a 2x3 table, in another sheet, like this:
TSB w/ 6.5% NaCl NS 29002
TSB w/ 6.5% NaCl NS 29003
TSB w/ 6.5% NaCl NS 29004
TSB..... in left column and NS...... in right column. This example includes 3 items as shown above but the single cell could contain more or less than 3 items
View 7 Replies
View Related
May 14, 2008
I want to add a Punctation mark (comma), like this: ,
and also want to add punctation mark (colon), like this: :
In this moment I have below macro:
Public Sub CombineCells
Dim Combined As String
Combined = ""
For Each Cell In Selection
Combined = Combined & Cell.Value & ":"
Next Cell
Selection.Cells(1, 4).Value = Combined
End Sub
the effect shoud be like this:
before:
--A
1-C
2-D
3-E
4-F
Etc.
after transposed:
--D
1-C:D,E:F Etc.
View 3 Replies
View Related
Nov 27, 2012
I'm using Excel 2010 and I'm having troubles displaying hiding my column B if none of the cells of column A contains either "(Quasi Echec)" or "(Quasi Russite)" at least once.
PHP Code: [URL] ......
Below are my 3 attempts.
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim c As Range
If Target.Column 1 Then Exit Sub
Application.EnableEvents = False
On Error Resume Next
[Code] ..........
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Sub GetUniquesInColA()
Dim rng As Range
Dim c
If Target.Column 1 Then Exit Sub
On Error Resume Next
[Code] .........
Code:
Option ExplicitPrivate Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim x As Long
Dim LastRow As Long
LastRow = Range("A65536").End(xlUp).Row
[Code] ...........
View 2 Replies
View Related
May 22, 2014
I am trying to read a value in cell A1 and make say cell B1 read the same. The data in cell A1 is a real time percentage value that gets updated every lets say 10 secs. Every time the data is updated the new value goes into the next cell A2, A3, A4..... and so on. Every time it gets updated (i.e into A1,A2,A3,A4.....and so on), I want to read the new value each time in B1.
View 9 Replies
View Related
Jul 2, 2007
How to copy an entire column having numbers and words on every single cell into another column but only copying numbers, not words.
View 9 Replies
View Related
Nov 29, 2013
New task for work today, which involves creating a spreadsheet for all existing members of an organization. I went to the organization's website and copied all of the names & info, then pasted into a blank spreadsheet. All of the entries were separated into their own rows, but all of the information is only in one column. Take a look at my sample:
(NOTE: does not contain actual names or info) Book1.xlsx
This sample only contains 5 entries, but my actual list contains about 200; if it had just been the 5, I would have been fine with manually separating the information, but for 200 entries I need something much faster. You'll notice in the sample that the company, person, address, phone number, email, and website (when there) are not separated by anything. I know that using Text-to-Columns, I would technically be able to achieve what I need using the Delimited option, but I can't imagine this working without separators. I thought perhaps there might be a way to separate them based off font changes or something? Or maybe some way that I can insert a semi-colon or some kind of separator between the necessary data?
While the font is Arial for the majority of the entries, in terms of font changes: company font size is 12 and color is navy blue; the person's name is size 18, bolded, and dark grey; the address & phone number are size 9 and the color is light gray; and finally, the email & website are also size 9, but navy blue in color. However, you'll notice that two of the five entries are formatted differently. This is because formerly, the entire cell & its text were a hyperlink to the email. I went ahead and removed these hyperlinks, simply using a "Remove Hyperlink" macro walk through I found on the web, but now these cells are uniformly set to Calibri and size 11, my default font setting.
View 6 Replies
View Related
Dec 19, 2011
Is there any method to speed up a for each loop to count text in a cell with multiple criterias in a single column. This is on example:
Code:
For each rr in r
If rr = "a" And rr.Font.Strikethrough = False Or rr = "B" _
And rr.Font.Strikethrough = False Then
a = a + 1
end if
next
View 1 Replies
View Related
Sep 5, 2013
I need a macro that will examine column A starting at A2 and working its way down copying the data in the first cell (A2) and then delete that row. If the next cells are empty (usually the next 5 cells) it should paste this copied value in each of the empty cells until it comes to the next cell with data in it. At this point it should copy this next value and delete the subsequent row and copy this value in each of the empty cells directly following (again, usually 5) until it finds the next cell that has data in it. At this point, again the whole process begins again repeating it self until it comes to the end where no more data is.
Here is an example of what I need done:
This sheet:
Sheet1
*
A
B
C
D
1
Item Number
Type
Type 2
Feature
[Code] ...
Should look like this:
Sheet1
*
A
B
C
D
1
Item Number
Type
Type 2
Feature
[Code] .....
View 9 Replies
View Related
May 8, 2014
I am looking to find all visible cells in column E that are blank, and then add ''B'' to those empty cells.
I am using code similar to the below:
[Code] .....
View 5 Replies
View Related
Jan 24, 2014
I am trying to come up with the most efficient way to copy data to multiple sheets within the same Excel workbook. The original data exists within one column on a summary sheet (could have up to 500 individual entries). I want to copy each individual entry to a unique sheet (that already exists), but in the exact same cell location within each sheet. I would only want to copy the original data value and not any formatting. Is there an efficient way to do this?
In my example spreadsheet, the original data is on the SUMMARY sheet. Sheets A through J would be the target sheets, with cell B2 as the target location for each of those sheets. My example shows the result of a manual copy paste value process, but I am hoping to automate that.
View 14 Replies
View Related
Nov 3, 2008
How to formulate Character
A1 = ABCDEFGHIJ
A2 = ABCDFGHIJ
B1 = IF character is 10, I take ABCDE (5 digit) but if only nine I'll take first 4 digit. How could I formulate that?
View 3 Replies
View Related
