I am looking for a formula to delete information from the middle of a text string. I have ~ 1,000 lines of data. Each text string is composed of the same way. The data I am looking for is two parts of the string (1) all the text up to and including the first “%” character (2) the last six characters.
Raw Data
CARDINAL HEALTH 5.5%13CARDINAL 5.513 CARDINAL HLTH 5.5 061513
UST BILL 0%09US TREAS BILL 0%09 UST BILL 0.0 092409
WORLD SAVINGS 4.125%09WORLD 4.125%09 WORLD SVGS 4.125 121509
JP MORGAN 5.375%12JP 5.375%12 JPMORGAN CHS5.375 100112
Desired Result
CARDINAL HEALTH 5.5% 061513
UST BILL 0% 092409
WORLD SAVINGS 4.125% 121509
JP MORGAN 5.375% 100112
I am trying to delete part of a text string for a range of cells. The code I have so far deletes the first set of letters but is looking in every cell in the range.
Ideally I would like it to only look in the cells of col B where there is data then only delete the first 10 letters if the cell begins with "Service ID"
Sub DeleteServiceID() Dim c As Range For Each c In Range("B1:B1000") c.Value = Right(c.Value, 10) Next c End Sub
I have a column of data with letters in each cell, no numerical, only alpha. Now, some of those cells contain the letters "adj sub" as part of the text string in each cell. "Adj sub" is always at the beginning of the text string. As an example, a cell will look like this - "adj sub mhm". I want to delete rows whose cell description does not contain "adj sub" as part of the text in the cell.
I have a text string with 3 data to be extracted and then split throu range, but only specific parts is needed.
string is like that: Name Surname - Number : ################ - Exp : YYMM - Centre etc etc....
Lets say its allways happens in cell A3, for example: Adrian Kukuwas - Number : 1235467890123456 - Exp: 1009 - Centre... I would like a macro:
*that runs only if string starts with a letter, then if so *takes Adrian Kukuwas to cell D3 *takes 1009 (the exp) to B3 and makes it look like 09 10 (or 09_10) *in A3 leaving only the number 1235467890123456 text formated (sometimes the copy source formats the cell for bad)
I am trying to remove a middle initial from a text string however not all of the cells have middle initials. In column B I would like to return the first and last name. If needed I can have the first name in column B and the last in column C then combine them.
Example John A Smith John Smith John Smith John Smith
I'd like to extract just "blue.red.yellow.green" to the following spreadsheet column, keeping the intermediate dots, but getting rid of the first/last words and their succeeding or preceding dots.
I've tried using RIGHT, LEFT, MID formulas unsuccessfully
Is there any way to remove the first part of a string of text in a cell and save the second part?
The first part of the text string is a team code that has a variable number of numbers, capital letters and sometimes spaces. The second part of the text string is a variable number of words in a team name that all start with a capital letter and have lower case letters. Every line has a different team code and team name.
The original spreadsheet also has a column with just team code. Is there a way of using this column to "subtract" the team code from the text string to just leave the team name?
I have data sets on multiple worksheets within a workbook (over 70). Based on the begining of a string, I need to count all instances with that begining, and if there are fewer than 12 instances, delete the entire row.
Data set example:
In this example, I need to count each instance of rows starting with 1/* - 9/*, and delete rows as mentioned. In other data sets the string might start Gi1/*, and so on. In the following code, if I do not loop, and only test against 1 value type, and ONLY run it once, it works. As soone as I try to loop through all possibilities, or run the macro a second time, it blanks out the entire worksheet, starting at row 3.
Dim c As Long c = Worksheets(i).Range("A" & Rows.Count).End(xlUp).Row With Range("A3:A" & c) If Application.WorksheetFunction.CountIf(Worksheets(i).Columns(1), "1/*") < 12 Then . AutoFilter field:=1, Criteria1:="1/*" .Offset(1).Resize(.Rows.Count - 1, 1).EntireRow.Delete .AutoFilter
I have also tried to concatenate a variable with my CountIf criteria, but that fails miserably.
I belong to a fantasy cycling league and after every race, I copy the race results to award each rider their points for the day. The site I visit most frequently lists the riders results in this format:
1. Alberto Contador (Spa) Team Sax Bank Tinkoff 4:05:00 2. Lance Armstrong (USA) Team Livestrong etc... 4:06:00
I usually copy the results to my spreadsheet then calculate each riders points.
The'results end up looking like this in Excel:
1. Alberto Contador (Spa) Team Sax Bank Tinkoff 4:05:00 2. Lance Armstrong (USA) Team Livestrong 4:06:00
So, the data ends up in 3 cells. The position in one cell, the rider and their nationality and their team in another, and the time of arrival in another cell
Is there a way in Excel where I can get rid of or delete all the data after a rider's name?
I usually end up pasting the rider information to Word, then replacing the (***) with a tab symbol, then pasting back to Excel and get rid of the data that's after the name by deleting the cells to which that data was transferred.
Is there an easier way to do this in Excel, or a macro I can create?...
Is there any way I can Vlookup for just part of a text stiring in a cell. I am trying to vlook if a part number contains the letters “HFR” if it does I want It to refer to the comment I have made in the second column of my vlook table against HFR.
i have a data as above, i wanna to extract the 250ml from the column 1, only 250ml (basically i want to extract the size code like 150g, 100gm from the data above).
I am trying to reference a Name of a place from an order number. To illustrate, University Park, IL can have an order # of 6598641373. The only thing is, all I need to reference is the first four digits, 6598. The other worksheet does not have city and state names, they only have the order #s.
I have to sort list of thousands name (3000 names) and remove duplicates. My problem is majority of the names have their title (i.e) Prof. Dr. Ir. Sir. etc typed in, so I need to copy the title in different cell and have their name only. Here's what I expect:
Current List ----> Column A -->Column B Drs. H. A. Andrew Boston, MRE. ----> Drs. H. --> Andrew Boston, MRE Drs. H. Andrew Smith ----> Drs. H. --> Andrew Smith H. Abd. Mohammed Junus ----> H. --> Abd. Mohammed Junus Prof. DR. Jane Doe, MD ----> Prof. D --> Jane Doe, MD Prof. DR. Ir. H. Randy Wong, MBA --->Prof. DR. Ir. H. -> Randy Wong, MBA Dra. H. A. Karen Patel ----> Dra. H. --> Karen Patel Drs. H. A. M. Kangkong ----> Drs. H. A. --> M. Kangkong Prof. Drs. H. A. Kareem Saleh, Ph.D -> Prof. Drs. H. A. ->Kareem Saleh, Ph.D Dra. Hj. Nina Schorder ----> Dra. Hj. --> Nina Schorder Ir. Abdul Jabbar ----> Ir. --> Abdul Jabbar
Also I have a Userform with 2 ComboBoxes named "ComboBox1" and "ComboBox2". Values in ComboBox1 is "Pull my pants", "Eat my shorts", "Socks for everyone".
What I would like to do is to search though column A and look for any of these textstrings in my selection in ComboBox1 and return the value from column B in column C.
Example: "Pull my pants" is selected in ComboBox1 then the value "pants" should be found in column A and value in column B (25) should be entered in cell C2.
I am fairly new to VBA and have spend hours searching Google and found some formulas like InStr and VLookup. My problem is that I am not sure if these statements will do the job and how to combine them.
I am trying to move through the worksheets and delete all columns with "Accession" in contained in them. I have to do this with an external macro as the spreadsheet with the data is created from another program. I tried this and get an 'Object or With block variable not set' error and the debug highlights the Cells. Find line of the code.
Also, the number of columns could be variable within the spreadsheet as it is compiled by the other program.
Sub DelAccessionNum() Dim Wrkst As Worksheet For Each Wrkst In ActiveWorkbook.Worksheets Cells.Find(What:="Accession", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False _ , SearchFormat:=False).Activate On Error Goto Completed: Selection.EntireColumn.Delete Shift:=xlToLeft Completed: Next End Sub
I am familiar with creating parameters and have been able to add things to the end of the string but not in the middle. I have been trying a very long time to figure this out.
What I want to do is delete a row if the middle column is less than 1.
However my loop seems to skip a row if the column data is like the below
a b c
1 1 1
1 0 1
1 0 1
1 1 1
1 0 1
End
Code: Range("A2").Select Do If ActiveCell.Offset(0, 1) < 1 Then Range(ActiveCell, ActiveCell.Offset(0, 2)).Select Selection.Delete Shift:=xlUp End If ActiveCell.Offset(1, 0).Select Loop Until ActiveCell.Value = "End"
I have an excel file, and on some rows, the "B" column contains the text " Total:" (it does have a leading space). I would like to loop through the first 200 rows (maximum length of the file) and delete all the rows that contain " Total:" in column "B". I have tried:
For i = 1 To 200 range("B" & i).activate If range("B" & i).value = " Total:" Then rows(activecell.row).select selection.delete shift:=xlUp End If Next i
When I step through that code, it just skips over the IF function as if B1 does not equal " Witness:"
I want to write a macro that is able to copy the 5th position to the 10th position characters from one cell to another. For example, in Cells(B1) contains: Mon Jan 8 09:00:02 2007. Then I want to copy "Jan 8" to Cells(A1). How do I do that? Also, i am not supposed to hardcode the cells. The codes should look something like that
start = InputBox.... end = InputBox...
For i = start To End Cells(i,"A").Value = (whatever Function To copy 5th To 10th char of Cells(i,"B"))....... Next i
I'm trying to create a VB Script for an AS/400 app. It's all written but I'm trying to feed the data for part from an excel file. If B5 was 10 I need the final output in my cell to read:
autECLSession.autECLPS.SendKeys "10"
Here is my current formula ="autECLSession.autECLPS.SendKeys"""""&B5*100&"""""" It's reading as autECLSession.autECLPS.SendKeys""10"" which has two sets of quotes not just one and no space between sendkeys and "10"
I have a several hundred rows of data that are telephone line type, number and user name all concatenated. This is the way the data arrives. I need to extract the number and user name into 2 separate columns - I do not need the phone type. Is there a formula that can identify numbers within text. I can't use Text to Columns as the leading telephone line type varies and I don't want to use VBA to do the work. There are at least 10 different line types, example of some:
SPECTRUM TIPT 0740357052 LUNCH ROOM SPECTRUM ANALOGUE 0744076642 J BROWN EXCHANGE LINE (PSTN/PSTN) 0742031346 HAYES SUB FAXSTREAM DUET 0742003232 NORTH SUBSTATION M/BANK (Spectrum) 0740318578 MB TO 4429 P PHONE (TELSTRA) 0744074159 B WILLIAMS WITHIN ANALOGUE 0784625967 I MAKIT
the following phones start witn an "N" PRIVATE LINE (PAPL) N7035896P ROTHMANS SUBSTATION PRIVATE LINE (VGDL) VOICE LINK N7563069P P BLACK PRIVATE LINE 2 WIRE PREMIUM (PAPL) N70079 FITZGIBBON G PRIVATE LINE 4 WIRE PREMIUM (PAPL) N70335 SUBS SSKLB
Can this be done or will I have to resort to code.
I need to extract text from the middle of a cell. I know you can use the MID function but with the complete function as i need to extract text string from Name to the end of their full name (I have highlighted below in bold). Of course their full name will change in each row.
Example text in cell B2: ID: 197993043 Name: Ben Fred Company: ID: 197992821 Name: Hello World!
