Taking End Text From Long String Of Text And Dot
Jun 14, 2013
I have a bunch of these following below which I need extract text from the first "." from the right hand side.
The amount of text and "." can vary as does the number of characters.
XXXXXXXX.XXXXXX.XXXX.XXX.XXXXX.XXXXXXXXXXXXX.XXXXXXXXXX.Vibrating Screen SC409
XXXXXXXX.XXXXXX.XXXXX.XXXXX.XXXXXXXX.XXXXXXXXXX.Scalping Circuit 4
XXXXXXXX.XXXXXX.XXXXXXX.XXX.XXXX.XXXXX.Fines Stacker ST601
Ultimately i want it to display the following:
Vibrating Screen SC409
Scalping Circuit 4
Fines Stacker ST601
I have used LEFT, RIGHT, SEARCH, LEN functions in various combinations but cant get it to reference the last "." from the right.
View 3 Replies
ADVERTISEMENT
Sep 27, 2013
I have two columns that read:
XXHSXX HR
XXTRXX MG
XXXFXX XG
XXHSXX HS
XXTRXX MG
XXMEXX RU
XXHSXX HR
XXHSXX
XXTRXX
XXTRXX
....
The above list represents 2 columns. The left column is 400 lines long. The right column is partially complete with 2 letter codes that represent the 6 letter codes on the right. I'm trying to convert the column in the XXYZXX format to a 2 letter column and each of those 2 letters corresponds to the 6 letter column on the left. How do I do this using any method or Vlookup? How does one convert a column of text to a corresponding column of text? I don't want to have to manually type in 2 letter codes that match up the left column. What do I do instead?
View 2 Replies
View Related
Sep 4, 2009
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.
View 9 Replies
View Related
Jun 25, 2014
I'm trying to run a countif formula in a separate worksheet searching for a long text string. The text string is long enough that I can't copy the entire string into the Ctrl F Find function.
=COUNTIF('Data Month 1'!C:C,F4)
F4 = text string put together using the values of multiple adjascent cells. Total characters are over 26.
View 12 Replies
View Related
Apr 17, 2007
I am trying to automate a report writing spreadsheet in which the text never changes in the cell (up to and over 50 words long).
Is there any way in which the figures within that text could be linked to another cell and change accordingly.
For example: a particular cell might contain the following which never changes apart from the figures:
"the performance of the fund was (B5) followed by a change in the index of (B6)...........the performance of the irish stocks was (A19) etc etc."
I know something can be done in formatting but for very long text would this be impracticle?
View 5 Replies
View Related
Mar 27, 2008
I have a spreadsheet that has a column of text that is always 10 characters long. There are 10 rows of text so there could be 100 text characters if all rows are filled. The rows usually will not all be filled. There will probably be blank rows between used rows. I have been able to capture the text and put it into one cell as one long text which is ok but I want to be able to break it up into the 10 character strings again, separated with a comma and space between each 10 characters.
This is the code I used to collect the 10 character text strings and put them all together as the variable "result". I used & ", " after ... Cells(r,17) which worked fine unless there was a blank row. If the row was blank it put in a comma and space anyway so I ended up with duplicate(triplicate) commas and spaces.
Private Sub test_Click()
result = ""
For r = 5 To 32 Step 3
'If Cells(r, 17) "" Then
result = result & Cells(r, 17)
Next r
Range("r5") = result
End Sub
View 9 Replies
View Related
Feb 26, 2009
I've got a sheet which I need to analyse and split into several different sheets but the raw data format leaves a lot to be desired as there are blank rows inserted randomly between rows of data. I need the 'good' data to stay in the same order so I've written a macro to sort through the data and delete any blank rows leaving the good stuff behind.
The problem is this takes ages as there can be up to 30000 rows that need to be checked and I need to do this 5-6 times a day. I just wondered weather there was a quicker way to do this? The code I've got is detailed below:
Sub Prep2()
'Delete all blank data rows
Dim Rows As Double
Dim Rownum As Double
Application.ScreenUpdating = False
Rows = Selection.SpecialCells(xlLastCell).Row
For Rownum = 2 To Rows
If Cells(Rownum, 11) "" Then GoTo NxtRownum Else
Cells(Rownum, 11).EntireRow.Delete shift:=xlUp
Rows = Rows - 1
NxtRownum:
Next Rownum
Application.ScreenUpdating = True
End Sub
View 9 Replies
View Related
May 28, 2009
I have a couple shreadsheets I have created, one is for Purchase order's and has 4 different sheets. with one sheet holding addresses linked a drop down menu in the first sheet, all in the same workbook. Also there is a macro within this sheet to print to a specific printer, and a specific number of copies when CTRL p is pressed. The total file size is approx 9 mb. Opening this file takes approx 30 to 45 seconds. There are no links to any outside files, only links within the workbook itself.
The second File I have recently started having problems with (Our Quotation log) is A workbook containing 3 sheets, 2 sheets independant and one sheet linked to the second that Summarizes the first and also has cels to enter information on the person the quotation has come from. I have this sheet formatted as a table so I can sort by name etc when doing followups.
This workbook also takes approx 30 to 45 seconds to open, and on occasion longer. All workbooks are stored on my computer. The PO workbook has had the problem since I created it, although the Quotation log workbook has only had the issue this week.
View 4 Replies
View Related
Jul 21, 2014
I have a data range with just under 100,000 lines by about 60 columns. In one of the final columns I used the formula =IF(COUNTIF($A$2:A2,A2)=1,1,0) in order to set a flag the first time that each unique value in Column A appears. (I know about the remove duplicates function but the data was sorted in a certain order and I needed information specifically from the first row in which each unique value appears.) The formula works - it's not very quick, but it works.
The issue I'm encountering though is that now I want to remove that formula so it doesn't keep recalculating and every technique I use either takes forever or stalls Excel entirely. I've seen the same issue in two different spreadsheets where I used the same technique. Techniques I've tried: selecting and deleting the entire column, copying and pasting values over the formula, Selecting all the cells and pressing delete, copying and pasting an empty cell over them, Selecting and cutting and pasting the cells to another workbook. I've done these while disabling automatic recalculation and even turning off Data Filtering. I just can't understand why a simple delete operation should take so long, it shouldn't matter what is in the cell because all I want to do is delete it.
View 3 Replies
View Related
Jan 11, 2010
I designed a spreadsheet which uses a lot of VLOOKUPs and it takes a very long time to recalculate. I don't know if there is another way I could do this, but this just seemed to make sense and it works just the way we want it but it just takes too long. Here is what the spreadsheet is designed to do:
We have data that is pasted into excel from another source. The data is broken up into account number, date, check number, and amount. Multiple payments from the same account can occur in one month so the data must be totaled. There is a custom function called concatif which works just like sum if but concatenates text. There are VLOOKUPs performed on the account number and the corresponding date, check number, and amount are inserted in the table.
View 3 Replies
View Related
Mar 15, 2012
I get distinct word with this formula i have 30,000 rows with below formula taking lot of time with dragging to all cell
{=INDEX(List,MATCH(0,COUNTIF($b$1:b1,List),0))}
View 5 Replies
View Related
Apr 18, 2012
I have a large file that takes a long time to close.
I would like to display a message that says " SAVING - PLEASE WAIT".
But I do NOT want any interaction from the user to continue so I can not use a MSGBOX.
How can I do this?
View 7 Replies
View Related
Aug 16, 2013
Is it possible to take text from 2 different cell and inserting into one cell?
For example:
Cell A1 reads 'John' and cell A2 reads 'Smith' can I make cell A3 read 'John Smith' by taking those two bits of information?
View 2 Replies
View Related
Jul 9, 2014
I have a set of task descriptions that I am attempting to trend on. Some of these (Column B) have the customer's name added to the description; others do not. I need to be able to make a list of task names (ColumnA), removing the name from the text string.
The formula I am using is [=LEFT(B3,FIND("for",B3)-2)].
The problem I am having is when the description does not contain the "for" built into the formula, I get "VALUE" error. What adjustment can I make to the formula to pull over the Description if the "for" does not appear in the text string?
View 3 Replies
View Related
Jun 29, 2014
I need to create a macro to do the following:
Search the activecell for a text string (a), and then either paste in text string (b) at the end of the cell if (a) is found, or text string (c) if (a) is not found.
For example, if the activecell has "AA/" in it, I want the cell to become "AA/01" (pasting in "01" at the end), and if the cell has just "AA" in it, I want it to still become "AA/01" (pasting "/01" at the end). The macro will be linked to a commandbutton.
View 7 Replies
View Related
Mar 28, 2009
refer to the attached workbook for reference. I am looking for a function in Sheet1, Column E that will search for the value of Sheet1, Column A within Sheet2, Column A. When a match is found, the function should look across Sheet2, Columns B - V for values of 1. When such values are found, the function should return the associated value from Sheet2, Row 2. There may be multiple values of 1, and as such, the function should separate values with a comma.
View 3 Replies
View Related
Mar 8, 2013
How 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.
View 1 Replies
View Related
Mar 29, 2013
I'm pulling phone numbers out of text strings. There is text string ("Office:") indicating that the number following is an office number (the number that I need). MID and FIND take care of this. There are sometimes two instances of office numbers in single text string, so adding a second column using the third argument of FIND lets me start another search past the first instance of an office number. We have a bunch of office numbers with the qualifier "(Text)" after the original "Office" but before the phone number. How do I catch these ones?
View 8 Replies
View Related
Apr 8, 2014
I have the cell data as below
How would I split into a new column the first part which is a date into a new column, then the country and the remainder into separate columns?
I still want the original data as I need to check that the splits worked well?
16.5.90 CH 1671/90-4
18.10.1991 CH 3056/91-1
24.07.92 ch 2341/92-2
30.7.92 ch 2395/92-3
18.11.92 Us 3533/92-5
26.5.93PCT 1577/93-0
9.8.93 CH 2363/93-8
17.8.93 CH 2445/93-0
25.1.94ch209/94-6;8.12.94ch3714/94-1
25.1.94 ch 209/94-6 ; 8.12.94 ch 3714/94-1
8.4.94 ch 1047/94-0
22.4.94 ch 1255/94-7
18.11.1992 CH 3533/92-5
18.11.1992CH 3533/92-5
View 2 Replies
View Related
May 12, 2012
I have a dataset of shifts and want to compare each shift that needs to be filled to a list of requests for time off (vacation, etc).
For one cell the code is: =IF(ISERROR(SEARCH(B1, A1), 0,1)
Where A1 is the column of requests in comma delimited forme (ie: "AB,CD,EF").
Where B1 is the column containing the person assigned to shift 1 (ie: "AB")
In this case, would return a "1" as no error was returned, as AB was found in the list. Here "1" would represent a schedule conflict.
Without creating many cells for each shift- there are 20 shifts- can I create an array or string together this type of "SEARCH" function?
View 1 Replies
View Related
May 15, 2014
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.
View 5 Replies
View Related
Nov 29, 2011
I am working on modifying this code (below) that I found here: VB Macro to search webpage for text string
Code:
Option Explicit
Public stResultstr As String
Sub SearchForString()
Dim rngURL As Range
Dim cll As Range
Dim stCheck As String
Dim xmlHttp As Object
[Code]...
So right now I have the script prompt you for a URL range, then it asks you for what you want to search for, and I have it set to return the next 10 characters from that found point.
You can see here:
Code:
If InStr(1, stResult, stCheck, vbBinaryCompare) > intt Then
intt = InStr(1, stResult, stCheck, vbBinaryCompare) + Len(stCheck)
I began to create something to identify the last position of the found item because next I want to loop it to find the next occurance of the same thing and return the next 10 characters after it in the next cell to the right in that same row:
Code:
cll.Offset(, 1).Value = stResultstr
Again, what I want to do here is find and parse out every occurence of a string (inputbox) found on a web page url in column A. The parse occurrences will go to the right in cells C though ? for row N.
View 2 Replies
View Related
Sep 17, 2013
Date: 17/09/2013 KO: 19:45 Ref: B Malone Att: 7,574 extraction of text from the above text string which is say in A1. What l need is for Date to go in A2, KO in B2, Ref in C2, & Att in D2. I'm using Excel 2003
View 7 Replies
View Related
Mar 15, 2007
Cell H1 has a variable string of references for eg
"FI570783AQ3516346EQ3516346FXVB123456"
I want to return the reference beginiing with "FXVA" so it would ignore the rest and only return FXVB123456 - this should always be 10 characters.
Just to add another complication to the mix, there may be 2 "FXVA" references in the string and i want to get both (these can be in the same string so FXVB123456FXEL123456 - but this might not happen regularly.
View 9 Replies
View Related
Mar 27, 2008
I have a sheet in which some of the cells have two strings separated by a linefeed. I have come up with a cumbersome formula which will let me check if either of the two strings is a member of a list stored on another sheet. However, it fails if there is only one string in the cell, presumably as there is no linefeed for the formula to find. How can I modify the formula to cope with this situation?
There are also on occasions, three strings in the cell, but I can't seem to access the middle string with the formula. Simplified spreadsheet attached to show the problem. This must be formula-based, as we have a no VBA policy. If you think there is better way of doing this, please let me know.
View 3 Replies
View Related
May 12, 2014
I have a report that in column BX has a large string of text (html information). Within this text there is a phrase called "| Hear = Education Website |" The phrase Education Website can be a variety of things though (Up to 9 choices). What is a formula I could use to just pull out the text after "Hear =" but before |""?
View 8 Replies
View Related
Jul 17, 2014
I have one column full of Item #s and Descriptions. I have another column that I want JUST the Item# in it.
For example:
Column 1:
Gyroscope REF#1234 Bike Thing
Column 2 Should Be:
1234
Column 1:
BallWall Bikes Ret# 12456 Helmet Thing
Column 2S hould Be:
12456
I don't know how to do this (I do know how to do VBA mildly, and I am pretty good with formulas). I also have a database of every Item# that could be in that cell.
View 6 Replies
View Related
Jan 31, 2012
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
View 2 Replies
View Related
Mar 12, 2014
I am trying to extract numbers after a specific text in a text string, for eg :abc SN 12345 xyzedf SN No. 456 mnoAs per above, i want to extract any numbers afters "SN". the numbers can be vary in digits i.e. it can be 3 digit numbers or 4 or 6. Also, at times there is some other text in between (like SN No.) numbers and search word (i.e. SN)Any formula to get result as "12345" and "456".
View 5 Replies
View Related
Oct 4, 2013
I have a long date and time text value in a cell such as "2013/10/02 07:43:39.39", where the fractions of a second are very important.
I can use cdate([text]) to convert the text string to a value, but only if i omit the seconds fraction: "2013/10/02 07:43:39".
Otherwise i get a type mismatch error.
Is there any way i can get the whole date/time string converted to a date value? The only way i can think to do it it at the moment is to convert the date/time, then divide the seconds fraction by 86400 and add the 2 values. Is there a better way?
Secondly, i have a large number of these date strings, typically >30k lines x 7 columns. Each string is preceded and trailed by a [space] character.
I can strip these extra spaces no problem. And, i can "loop" to perform it, again no problem. But it's going to take time to perform and i'd rather not if i can avoid it.
View 2 Replies
View Related
