VBA String Manipulation
Sep 1, 2009
I need to be able to take someones name which is in a single cell in the format "First Middle Last" (could be more than one middle name) and then split it up so that it fits the following format:
Last name (must be 50 characters)
Middle name (must be 15 characters)
First Name (must be 15 characters)
I'm unsure on the right combination of operators to use to variably concatenate strings in this way. I'm confident I can figure out how to add the white spaces after a name to make it fit the character requirement but I don't know how to split the initial name up into it's 3 parts. Names longer than the character requirement will just be truncated.
View 9 Replies
ADVERTISEMENT
Jul 23, 2009
I have a macro that loops through a column of text and removes all text after a hyphen. Example: AU9929-PK becomes AU9929. I need to set up some exceptions that when it processes certain text it does not truncate it. Example: AU9929-ASST stays as AU9929-ASST. So far there are twelve exceptions but this could increase.
Note: The list which includes shop number, description, sku, sku description, quantity, unit price and more is sorted in a particular order as other operations are performed on it that requires it be in a specific order.
View 4 Replies
View Related
Aug 1, 2008
I am trying to let the user call my subroutine in the following 5 ways but am lost using string manipulation -
Call FindReplaceAll(C, 123, 1.23) In Col C replace 123 with 1.23
Call FindReplaceAll(2, 123, 1.23) In Row 2 replace 123 with 1.23
Call FindReplaceAll(C:E, 123, 1.23) In Col C-E replace 123 with 1.23
Call FindReplaceAll(2:4, 123, 1.23) In Rows 2-4 replace 123 with 1.23
Call FindReplaceAll(C9:E19, 123, 1.23) In region C9-E9 replace 123 with 1.23
Here is what I am trying to do in psudo
Sub FindReplaceAll(sType As String, sFind As String, sReplace As String)
If sType does not have a colon in it then
If sType = A-IV or sType = a-iv only then
Columns(sType &:& sType).Select
If sType = 1-65000 then
Rows(sType &:& sType).Select
Else
View 9 Replies
View Related
Dec 23, 2009
I have a sizable spreadsheet, which has one column where the cells are mostly numeric strings of up to 6 numbers - there are variations, which I will mention.
What I need to do, is to treat the numbers as individual figures, and add them, then divide by the number of figures. e.g. cell value '123456' I need to process as (1+2+3+4+5+6)/6.
The variations are that '0' has to be added as 10, and any letters need to be added as 12. '-' and '/' which are the only non-alphanumeric values should be ignored.
View 9 Replies
View Related
Dec 5, 2008
i have a worksheet called 'week 1' and want to write vba code so that a new sheet called 'week 2' can be created. However i want to write code so that it doesn't matter what sheet i am on, a new sheet would be created with the name week and the next number up. ie. the last worksheet is 'week 7' then the code should be able to create a new worksheet called 'week 8' ect. I currently have this code but it keeps returning an error message. anyone know how i could do this???
Sub nSheet()
Dim nm, i As Long
nm = Worksheets(Worksheets.Count).name
i = 1
Sheets.Add
Do While WorksheetExists(Left(nm, Len(nm) - 1) & i)
i = i + 1
Loop
ActiveSheet.name = Left(nm, Len(nm) - 1) & i
End Sub
View 4 Replies
View Related
Dec 30, 2009
Column A
Column B
Column C
Economy
BRITZEconomy
Question
Compact
BRITZCompact
What formula do I need to use in column A to get the desired results as per Column C?
Medium
BRITZMedium
Large
BRITZLarge
Bus
BRITZBus
Bikes
BRITZBikes
4X4
BRITZ4X4
View 9 Replies
View Related
Oct 6, 2007
I am looping through and copying screens of mainframe data and appending them to the clipboard. Once done, I start a Notepad and paste it for future massaging. Now I need to perform a check on each screen which requires copying something from the screen, assigning to a variable, and checking it. This overwrites what I've already stored on the clipboard. Is there a way to store what I've got on the clipboard, then use the clipboard for something else, and then reload the clipboard?
View 3 Replies
View Related
May 25, 2009
The data is arranged as such, there are a list of call types and in another column, there are a list of call lengths. Currently I have a COUNTIF which searches the call type column for certain call types ie BA is off peak but this only tells me how many off peak calls they have made, I need to make it tell me how many minutes of off peak calls there are.
IE this is an example
Call Types Call Lengths (minutes)
BA 1
BA 1
BA 3
BA 2
at the moment my formula would only count this as 4 but I need it to calculate it as 7. This is a very big data set containing around 900 entries per sheet.
View 11 Replies
View Related
Nov 14, 2012
Below I have a table with dates. The actual dates will be added in over time. I want to display the amount of days in the column btwn planned and actual but since it is subtracting a date from a blank cell, the #VALUE function is displayed. What function can I use to tell excel to keep the cell blank until a value other than #VALUE appears.
PLANNED Spud
ACTUAL Spud
Days
5/30/12 12:00 AM
5/30/12 2:30 AM
(0)
12/15/12 12:00 AM
#VALUE!
1/19/13 12:00 AM
#VALUE!
2/23/13 12:00 AM
#VALUE!
View 1 Replies
View Related
May 13, 2007
With my limited knowledge I am having trouble constructing formula to break the following info that I receive frequently from my credit card company into 3 columns - Date, Details, Value
21 Apr 2007 SAINSBURYS PETROL BURPHAM £10.00
View 9 Replies
View Related
Jun 22, 2009
Is it possible to create a formula that looks at a cell (say A20) and if TRUE changes the colour of A22 to green if false to red?
View 9 Replies
View Related
Oct 25, 2006
I have a file with a physician name and title in the cell as follows:
"James A. Jones, M.D."
I need a formula to reverse the last name, drop the title "M.D.", get rid of any additional periods after the middle initial and put it in upper case:
"JONES, JAMES A"
There are some names without middle initials also so this may present an additional problem.
View 7 Replies
View Related
Jan 16, 2007
I have test.txt file. The data in test.txt file so messy (there are headers and some data lines start with letter "A" and need to ignore). There's no <TAB> in data file, all are single space and <enter>. I can do nothing about data files.
Task:
Open test.txt file
SUM all product quantity, put result in xls file in A1
SUM all Total price in the txt file, put in xls file (same file) in B1
Close test.txt file
View 4 Replies
View Related
Aug 4, 2009
Example:............
Where all question 1 fields are put into a row, 2 in another row, etc (this may be more than 2 sets of questions), grouped by the name field. I have had a look at Pivot tables but they dont seem to do what Im after.
View 3 Replies
View Related
Sep 17, 2009
Im Creating a email based on useform control values. Here is a part extract of how im creating it.
View 4 Replies
View Related
Apr 27, 2007
My pivot table is not auto summing each column properly, and I can't manually enter a formula to do so. I tried adjusting the settings of the rows and columns through the pivot table wizard, but no luck. Is there a way around this limitation?
View 6 Replies
View Related
Sep 2, 2006
I have a program that updates values quarterly when run. The spreadsheet that it works with also contains a couple of pie charts that correspond to the updated data. the charts only have one series with category values. I recorded a macro to see the code excel uses for creating a new chart and tried to modify the statements to my needs but have been running into runtime error 1004 ("Method ' Cells' of Object '_Global' failed"). The code is as follows I don't have any trouble until the last two statements.
Sub Chart_Updater()
Sheets(Chart1var).Select
With ActiveChart
.HasTitle = True
If Chart1var = "COLI VUL 1 Fund Chart" Then
.ChartTitle.Text = "COLI VUL 1 Allocation by Fund Provider " _
& sday & "-" & sday & "-" & Lyear
ElseIf Chart1var = "COLI VUL 2 Fund Chart" Then
.ChartTitle.Text = "COLI VUL 2 Allocation by Fund Provider " _
& sday & "-" & sday & "-" & Lyear
ElseIf Chart1var = "COLI VUL 7 Fund Chart" Then
.ChartTitle.Text = "COLI VUL 7 Allocation by Fund Provider " _
The variables fundtr1, fundtr2, and emptycolvar are public, and are all returning values, so there is no problem there. Also I can't use ranges like "A1:F7" because the range needs to be variable.
View 5 Replies
View Related
Oct 12, 2009
I've created a humongous spreadsheet in Excel- 26 million cells and a file size of about 500mb. The result of these calculations is a pattern derived by conditional formatting. I'd like to get the pattern into Photoshop- It would actually simplify image manipulation in Photoshop to lose the cell data, and the underlying formulas are of no use.
The file seems to be too big to save into a PDF file. Is there another way to get the Image into Photoshop?. The other alternative is to reduce the file size by stripping out unnecessary cell data and formulas without losing the pattern.
View 2 Replies
View Related
May 19, 2014
I have a table which is extracted from a database using 2 tables.
1 Header and 1 details table.
I want to display the latest date in column E.
As Column A,B,C is the header table and column D,E is the details table.
Can this be done in vba or a pivot?
View 1 Replies
View Related
Mar 15, 2013
Data manipulation question here: how to dynamically filter and sort a multi-column data set? My end goal is to be able to (1) quickly collapse all columns into one single column, (2) remove all duplicates, and (3) sort the information in ascending order. A reference sheet is attached in case it's useful.
View 5 Replies
View Related
Apr 27, 2007
I have a small table, 6 rows, 2 columns which looks something like:
Apples 3
Pears 1
Bananas 0
Cherries 2
Oranges 1
Grapes 1
The total sum of column B will always be 8.
What I want to produce is a range, say d1:d8 which reproduces the data in a format like:
Apple
Apple
Apple
Pear
Cherry
Cherry
Orange
Grape
i.e. responding to the number of items in the former table specified.
I'm sure it can be done with some long stringed If statements mixed with counts, but do any of you have any good ideas for formulas in d1:d8?
View 9 Replies
View Related
Sep 19, 2012
I have a udf that returns a string to the cell. The string is made up of multiple string "objects". What I am wondering is if I can set the font color of certain objects so that when the final string is built and returned, the font of those portions is set.
Ex. of simple idea (this is not actually my code, just a way to illustrate. I realize there is no point to this UDF):
VB:
Function StringReturn (Str1 As String, Str2 As String, Str3 As String) As String
StringReturn = Str1 & Str2 & Str3
End Function
Now what if I wanted Str1 and Str3 to be blue, and Str2 to be red for example. So that when the UDF calculates it would return: Str1Str2Str3
View 2 Replies
View Related
Aug 14, 2014
Wondering if it is possible to generate a random 4 digit number based off an alphanumeric string?
Example;
Cell A1 has 123XVF1234
Cell A2 has 321AFW4321
In B1 I would like to have a 4-6 digit number that is generated based on the alphanumeric data in Cell A1 (and so on down the list). If that is possible, I would also need to be able to convert back the 4-6 digit number back to its original alphanumeric value
Example;
If B1 returns 643562 it would need to be able to be converted back to 123XVF1234
View 7 Replies
View Related
Apr 7, 2014
In sheet1 I have a simple database consisting of 5 columns of data
Column A : Name ie James Jones
Column B : payroll number ie 123456
Column C : shift times ie 1245-2124
Column D : job title ie floor
Column E : comments ie A/L or 0600-1500
what I would like is some code that will go down Column E and if a 'time string' ie 1300-2130 is found then copy this string and paste into corresponding value in column C. If a text string is found ie A/L or Sick or anything like this then ignore and move onto next cell, loop this until all cells in column E have been checked.
View 4 Replies
View Related
Jan 10, 2009
I need to create a string of all the Acc Number but I dun not wan any repeat of the same Acc number in the String . The number of Acc number to be put in the string may varies sometimes. It may varies from 3 to 1000 or more . Is there a way to write a general formula to create such string?
View 4 Replies
View Related
Aug 27, 2009
I have a situation where a word (in this case "Restaurant") is misspelled in a list of about 78,000 location names. The location names are in one column, and the [misspelled] word "Restaurant" is anywhere between the first word of the string, to the last, with any amount of alphanumeric/symbol characters between. For example:
Alice's Restaurant
Alli's Restaurant & Bar
Alexis Restaurant of Waukesha
Amigo's Mexican Restaurant #2
I want to replace any misspellings of the word with correct, but since the list is so long, and the way the word is misspelled varies so much, going through manually is entirely too time-consuming.
Some of the variations I've seen so far are Resta, Restau, Restaur, Restuara, etc...
Is there a way to search and replace cells that contain the text in any location of the string? Specifically, where ever there is JUST "Restau", replace with "Restaurant", regardless of where it is in the string? This way, trailing text is not deleted or manipulated, i.e. "Alice's Restaur and Bar" will change to "Alice's Restaurant and Bar".
View 14 Replies
View Related
Apr 11, 2013
I have a spreadsheet which has "Employee: [agent 1 name]" in column A and it may or may not have the word "Break" in the same column before it mentions "Employee: [agent 2 name]". The amount of data between agent 1 and agent 2 varies and am needing code which will insert a row above "Employee: [agent 2 name]" if "Break" is not found, and add the word "Break" in column A on the inserted row. I would need this to loop through the spreadsheet until all 100+ agents have been searched.
I'm also needing this done for the word "Meeting" and would insert a row 2 rows above the next agent.
View 6 Replies
View Related
Feb 18, 2014
I'm using a userform to create a new sheet. The form already creates the sheet and names it what was typed into the userform. Now I want it to place that variable in a cell along with a string. the following code will place the variable from the form (tbname) into cell b5.
View 2 Replies
View Related
Sep 4, 2012
I am working with flight numbers and want to split out the letters from the digits. Examples,
BA1234A
BA123
EZY4566H
BE7893B
In column A I need the first two or three letters only,
BA
BA
EZY
BE
In column B I need everything to the right of what appears in column A
1234A
123
4566H
893B
etc.
View 7 Replies
View Related
Dec 22, 2013
I have the following type of info in A1,A2,A3...
Code:
nameGaryage40cityPittsburgheight190
age30height150
ameLindacityMichigan
citySan Jose
ameHarryheight180age50
My goal is to get as close as possible to this,so it will be easier to sort and manage
Code:
nameGaryage40cityPittsburgheight190
nameLindaage30cityMichiganheight150
nameHarryage50citySan Joseheight180
I can't use the "" sign as delimiter to separate them into different columns because the age,city,name and height fields are in random positions on different cells.The good thing is person's name will always come after "name" string, age is alwals followed by "age" string, so it cannot be like nameheight40Michigan180
I think the following would be the easiest method(not for me tho).If on B1 I had a formula that said "find the string "name" and write anything after it until you reach the next "" character".On C1 field I could have a formula "find the string "age" and write anything after it until you reach the next "" character.On D1 I would have the same for "height" string,then on E1 for city string.
My question is somewhat similar to this one
Extract A String Between Two Characters
Formula which outputs the data between 3rd and 4th instances of the "_" character.Can we substitute "3rd and 4th" with a specific strings like "age" or "height" ?
Code:
=TRIM(LEFT(SUBSTITUTE(MID(A1,FIND("|",SUBSTITUTE(A1,"_","|",3))+1,LEN(A1)),"_",REPT(" ",LEN(A1))),LEN(A1)))
View 3 Replies
View Related
