I have a database with Column "A" that looks like this:
"A"
PKJHB
PLKFSDA
KJGFSA
LJKDAS
GKKA
I want a code that would add ".pv" to every cell in "A" Column so it'll look like:
"A"
PKJHB.pv
PLKFSDA.pv
KJGFSA.pv
LJKDAS.pv
GKKA.pv
I've got a sheet in which I want a drop down box, to ADD the value* to a cell, not overwriting its current value!
*The name of the selected option in the drop down box, the names are located in Map3!A1-n, I set the drop down box to display the related number in a cell next to it.
The cell would contain some text, and by selecting something in the drop down box, it would add the name of that option to the already existing value in the cell.
So if at first the cell's value is
Hi! I 'm Mark,
and you select the following option from the dropdown box
I 'm from Holland!
the cell would end with the value
Hi! I 'm Mark, I'm from Holland!
This would probably work with a macro, already made a start with it but I couldn't get it to ADD the value instead of overwriting it.
I am having problems adding data to exisiting text in each row of column E. The following macro is replacing the current text in each row with "V-1954". I need it to add "V-1954" to the begining of the text in each row of column E not over write it.
Range("D2").Select
Do Until IsEmpty(ActiveCell.Value)
'Fill in Column E
ActiveCell.Offset(0, 1).Value = "V-1954"
'Move down one cell
ActiveCell.Offset(1, 0).Select
Loop
I would like to append data from Columns A-F to a text file. The key thing is that the data needs to be tab separated just as it would be if I simply highlighted it and copied it and the pasted it into the text file.
View 5 Replies View RelatedI have a list of number in a column that there is over 22,000 is there a away I can add ".jpg" ad the end of each number?
View 10 Replies View RelatedI am looking for a macro to convert a 6 digital serial number into an 8 digit serial number by adding "00" to the front. ie. The cell may say 123456, but I need to change it to display 00123456.
Is there a simple way to do this?
I have a simple code that should insert the text at the beginning of the text file (the text file already has some text in it). When i use the following code, it just appends the line to the end of the text. I need it to append it to the start of the text file.
Code:
Private Sub CommandButton1_Click()
Dim file As String
lastrow = Range("D65536").End(xlUp).Row - 4
file = "C:Users11126923Desktop est.txt"
Open file For Append As #1
f = "hi test3"
Print #1, f
Close
End Sub
I want to open multiple .csv files from a single directory and append them to one workbook. The following code partly works, but appends only the first line from each file.
Sub GetFiles()
Dim w As Worksheet, fn As String, k As Long
Application. ScreenUpdating = False
Set w = ActiveSheet
k = Cells(65536, 1).End(xlUp).Row
If Not IsEmpty(Cells(k, 1)) Then k = k + 1
fn = Dir("*.csv")
While fn <> ""
Workbooks.OpenText Filename:=fn, Origin:=xlWindows, StartRow:=1, _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, Comma:=True, Space:=False, Other:=False
Rows(1).Copy w.Rows(k)
k = k + 1
ActiveWorkbook.Close False
fn = Dir
Wend
Application.CutCopyMode = False
End Sub
I write a daily status report that adds my daily comment to a cell with previous text in it. I then paste it in three other cells. This process is slow and tedious since the text in the cell is now becoming extremely long due to organizational and managerial restraints of the existing format. I use cut and paste and manual enter, a alt + enter, to space new comment. I would like to be able to enter the text in a cell and have it update the comment cell with the text in it and to update the text box. I have reviewed the forum and have yet to find the answer and use of how else to pose the questions.
View 5 Replies View RelatedI need help looking at text in a cell that has [] around it such as [big red trucks] and copy that text to the end of the cell and replace the "space' between the words and add '+' signs so the result looks like [big red trucks] [big+red+trucks].
example:
This is what the cell looks like before
[big red trucks] cost 5000 in store
This is what the cell needs to look like after
[big red trucks] cost 5000 in store[big+reg+trucks]
there may be additional text after the ']', I need the phrase with the + signs copied to the end of the cell
I have a filename written in cell A1- eg dog.jpg - and I want to insert a set character string just before the .jpg part. ie so it becomes dog_test.jpg in B1. How would you guys do it? I've been thinking of really long-winded methods that'd use several cells, but I reckon there might be a cleverer way!
View 5 Replies View RelatedI have data that covers multiple rows that I need to merge into one row.
Header:| InvoiceNumber | Type | Name
Row1: | 1000 | Payor | Doe, John
Row2: | 1000 | Payor | Smith, Mary
Row3:| 1000 | Payee| Jones, Henry
Row4:| 1000 | Payee | Jones, Bob
I need to get those four example rows down to one row such as:
Header | InvoiceNumber | Payor | Payee
Row1: | 1000 | Doe, John & Smith, Mary | Jones, Henry & Jones, Bob
The number of payor/payee can vary between 1 and several. I need some way to loop through and keep appending the names in one cell separated by a "&". And then deleting all the duplicate rows (based on InvoiceNumber).
I want to copy a bunch of data from a text file and paste in into an excel worksheet I have open. I want to paste it at the end but I don't know how large the data range will be each time so I can't select that size range. I have this code so far:
FileToOpen = Application. GetOpenFilename("Text Files (*.txt), *.txt")
If FileToOpen <> False
Then
Workbooks.Open FileToOpen
Else
Exit Sub
End If
Cells.Select
Selection.Cut
Windows("myfile.xls").Activate
Range("A1").Select 'THIS IS THE OLD CODE FOR THE FIRST IMPORT
ActiveSheet.Paste 'I NEED TO REPLACE THIS WITH THE CODE FOR APPENDING
'OR PASTING AT THE END OF MY RANGE
how to Chk the text string in particular cell, compare it with a super set column and get the full from of the text string from another corresponsing column and the output will be corresponsing full form of the chked text string?
View 6 Replies View RelatedHow do I use an Excel formula to find which (if any) multiple sets, each of up to 50 words, exist in a series of rows of a spreadsheet - if set A has one or more words found in a searched cell.
A positive result will return a specific value in the designated result cell. If none of the words in Set A is found in the searched cell, the formulae will repeat the test for the words in Set B, and so on.
After all 50 sets of words have been tested, the formula will move to the next cell in the searched column.
New words will be added to the sets of words continually as required.
Multiple words within sets are included in double quotes. Within each set of words there will be some n-tuples of words (i.e. 24 adjacent words) that contain one or more of the words in the set, but for which the formula will be required to return a negative result. Example: Set A = word 1, word2, word 3, "word1 word2 word3". (The words within a set could also be each entered in separate columns, as opposed to all included in a single cell.) The single column of text to be searched is about 10,000 rows.
I am wanting to use the above in a spreadsheet that contains data downloaded from a series of bank accounts to automatically allocate items of expenditure to one of 20 or so different categories of expenditure.
The formula will search the description field to find words that are used in the in the downloaded files from the various accounts to describe each transaction.
If a word describing travel expenditure (e.g. hotel, "holiday inn" but not "holiday travel") is found in the description of an expenditure item - the item cost will be allocated to the TRAVEL EXPENDITURE column, which is one of 20 or so different categories of expenditure.
Happy to consider a different solution if the task can be done better a different way.
Tried using a combination of INDEX/SEARCH/IF in Excel, but was not able to get a correct result. PS I am using Excel 2011 for Mac - which does not allow macros, so the solution needs to be entirely formula based.
I try to write the function that gets some "ref" and returns appropriate item.
For example: if I give to function "C3", it returns "AAA"
if I give to function "R18", it returns "BBB" (cause it between R15 to R26)
if I give to function "R9", it returns "BBB" also.
An original text string entry appearing in an Excel cell would be:
"N7C Neuroprotective J5Z Antiviral, other M2Z Antiarthritic, other J5A Antiviral, anti-HIV"
I need to extract N7C, J5Z, M2Z and J5A from this string and list these alphanumeric values in separate cells adjacent to the original text string. The challenge is that these alphanumeric references may appear in different positions within the original string with no fixed value e.g. a "," separating them. The alphanumeric references may also be 3 or 4 characters in length and there may be different numbers of alphanumeric references in the original string.
Another example would be (very different from the first):
"T2Z Recombinant, other K5B Radio-chemoprotective J3Z Antibacterial, other D3A Vulnerary A10C Symptomatic antidiabetic K6Z Anticancer, other R8A Antiasthma B6A Septic shock treatment I1Z Immunostimulant, other S1Z Ophthalmological R8B Antiallergic, non-asthma M1A1 Anti-inflammatory"
You can see that in this further example "A10C" & "M1A1" are 4 character alphanumeric strings wheras the others feature 3 characters.
I receive 24,000 text files once a month that need to be combined into one csv/txt file and/or spreadsheet(tab).
About a year ago I posted a thread on the same topic which received a fantastic response from jindon that worked great
Unfortunately, the format in which the text files are ouput has changed, as has the filename layout. The files are now output with filenames such as:
(lic#, company name, displaying # records found, date, type.txt)
40298827_Windham Professionals Inc _Displaying records 1 through 10 of 100_041813_AGENTS.txt
40298827_Windham Professionals Inc _Displaying records 11 through 20 of 100_041813_AGENTS.txt
40303726_HEARTLAND CREDIT RESTORATION INC _EANF_041913_AGENTS.txt
(files with EANF in the filename have no records inside them and can be skipped)
While the contents of each file look like this: (see attached text file reference)
I would like to combine the contents of the text files while appending the lic#, company name and date from the filenames to each record so the resulting file looks like this:
40305196 Audette , Anthony Sales Provider 40298827 Windham Professionals Inc 041813
40313800 Burritt , Kimberly Sales Provider 40298827 Windham Professionals Inc 041813
As far as I can tell jindon's code is fine except the regex expression needs to be modified to handle the new layout, however that is far beyond me.
Is there a formula that will add a number, in sequence, to the end of a text string to avoid duplicates?
I need to generate an ID number for transactions. This ID number is the Account Code-Last Two #s of the Year-Unique 3-Digit Number. So for instance, 5022-14-001 means it is the first transaction from account 5022 in the year 2014.
Column A has the Account Codes. Column B has the date of the transaction in MM/DD/YYYY format. So far the formula I have is:
=CONCATENATE(A1,"-",RIGHT(YEAR(B2),2),"-",TEXT(????,"000"))
With ???? being some function or set of nesting functions I need to create the sequential number. It needs to be able to say "Okay, this is the third instance of there being a 5022-14, so we need to stick -003 at the end of this."
Additionally, this "003" needs to be frozen, so if we change how the sheet is sorted and the line item moves around, it will still always be "003".
I have a column with a general format that looks like this: "057828001 - WACS - Irving".
I need to remove the latter part of the value " - WACS - Irving"
I am doing that by using the replace all and typing in "-*"; that gives me a result of 57828001.
I need a result of 057828001.
How do I retain the leading zero?
I have a large .txt file that I would like to edit using VBA. What I would like to do is to find each of the following text strings in the text file :
De:
De :
Envoye
A:
Objet:
If the character right before those text strings is not a carriage return (chr(13) then add a carriage return.
I would like the code to read each line in the text file and execute the task mentioned above.
What I have is a column of data(text) which contains amongst all the text three strings of text in ever cell in the column which I require copying into three adjoining cells
The data I require is :-
(a) The persons name which is always after the word ‘Requester’ e.g. Requester Steve Robinson
(b) Their office location which is directly after the persons name and is in brackets e.g. (Newcastle User)
(c) The Approving persons name which is preceded by ‘Approved by’ e.g. Approved by Christine Hunting
See examples 1 & 2 below
Example 1
CR0/CRZ3651 Requestor Steve Robinson (Newcastle User) Tel: 01234 798157 Approved by Christine Hunting
Please install and configure 2 Ultra 2s (typhoon and lancaster) for use as ARTE workstations. These workstations require Solaris 2.5.1 plus the same patches as before
Example 2
CR0/CRZ3118 Requestor Doug Cunningham (Newport User) Tel: 0114 9881480 Approved by John Smithers
Please provide support to set up Cisco 2691 Router and PIX-506E Firewall to enable external connection of a remote terminal for project work.
As you will appreciate the text in the cells is of non standard lenght and the three pieces of information can be located virtually any where in the text
Different formula in different cells give me some text as output, to format this. I would like to copy and paste in one cell, so that I can easily then register a macro on top to wrap the text in one big cell.
View 1 Replies View RelatedI have a column filled with names per row. I wanted to standardize it by using the following format.
The comma after the last name is always an indicator to me that it is the last name.
<First Name> <Middle Initial(if any)> <Last Name>
My actual records contains the following:
BIMBO EPPING - OK
AHMAD SADICK - OK
Salana, Laura R - need to be arranged to > Laura R Salana
Cantos, Rhoda Grace J - need to be arranged to > Rhoda Grace J Cantos
Can the below be achieved with any formulae or macro -
I have 2 worksheets (sheet1 & sheet2). In sheet2, i have some colors in column A (e.g. "red" in A1, "blue" in A2, "green" in A3.
In sheet1, i have some text strings in column A, e.g.
A1: I love red color
A2: my pen in blue
A3: Green grass
A4: Orange juice
A5: I like red and blue
What i want in corresponding cells in column B, is the name of the color which is in column A. so my output should be
B1: red
B2: blue
B3: green
B4: "NIL" (any error message)
B5: red (even though it contains 2 color, i am happy to show any one of the colors it contains)
n cell D3, enter a reference to the value in B7. Format the cell to display the text string Interest Rate.
In D3 i entered =B7 and the value for B7 popped up in cell D3, I am confused on how to enter the text string.
What can split up text strings into two cells.
I have this in A1
"X,Y Coord Dn = ",38033.8,42701.7
I have this in A2
"Bearing = ",128.9
I want to split this text into 2 cells.
The Formula I am using for the B1 is:
=IFERROR(LEFT(A1,SEARCH(""",",A1)+1),"")
result = "X,
The Formula I am using for the C1 is:
=IF(A1=B1,"",RIGHT(A1,(LEN(A1)-SEARCH(""",",A1)-1)))
result=38033.8,42701.7
The Formula I am using for the B2 is:
=IFERROR(LEFT(A1,SEARCH(""",",A2)+2),"")
result="Bearing = ",
The formula I am using for the C2 is:
=IF(A1=B1,"",RIGHT(A1,(LEN(A1)-SEARCH(""",",A1)-1))) result=128.9
I am using the same formula down each column; But i can not seem to ge the "X,Y Coord Dn = ", to work properly.
Column A has prepopulated list of values of various lengths, no spaces. Need to search column A for certain values at the END of each text string, and then populate the cell right next to it with another constant value.
Example: macro searches column A for cells with "123" at the END of the cell value (whole cell value could be A123 or ABC123, etc...). When macro finds the cells that meet this criteria, it places the constant value "XYZ" in column B right next to the cell in col A.
I need several variations on this theme and the # of characters being searched for at the end of each text string can vary. The constant value to be inserted in Col B can also vary. I imagine 'For Each' loops for every variation would work, I just do not know how to write the code for searching the "end of the text string" part of it
How would I write a formula to Countif or Sumif a cell contains a string of text? ie if I wanted to look for "Andrew" in cells that contained the text "AndrewKent", "Andrew Kent", "Kent Andrew" etc how would I do it?
I have to cell values that are strings and I want to add the two string values together to form a sentance, when I do this using a + sign or a & sign there is no space between the 2 strings, how do I get a space?
View 3 Replies View Related