is there a function that will split a text string based on a symbol. I know how tyo use left and right, which are based on a set number of characters but I want to split based on a "/" mark. whats to the left of the "/" mark and whats to the right of the "/" mark. any ideas. an exaplme is: tom / tim. i want a formula that will put the word "tom" in a cell and another formula that will put "tim" in another cell.
View 9 RepliesI am wondering if there is a symbol for "text" that I can use in a formula. I.e., =if(C="text", "", INDEX(C:C,MATCH(D16,A18:A23,0)))
View 4 Replies View RelatedI 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
I have been trying to work out a formula without success so far. If you look at the this text string
Code:
Depart Doncaster Depot with a Mini bus at 0649
The "Mini" word in red is the one that I am trying to work out with.
Here is the problem. The "Mini" word could be replaced with 7 other words - Walk, Artic, Dart Smart, Maxi, Smart, Staff Car, or just bus with no explanation as to what type it is.
I need a formula to look at the text string and work out which one of the above categories are included in the text and based on that gives me either W, A, Dart Smart, Mx, S, Car, and the last to be just N.
I am trying to add a * at the end of a value in a cell, used to signify a note at the bottom of the page. However, as the cell contains a formula, excel thinks I am adding a multiplication sign instead.
Is there anyway to place a * at the end of the value, to show up in the cell as text? I would prefer not to have to paste the value of the cell and then add the *...
I have a spreadsheet with Approx 900 rows of information that has been entered incorrectly.
Spreadsheet has 6 columns.
Contact Name
Contact Number
Company Name
Company Account Number
Order Date
Order Numbers
Every Order should have its own line - However I have approx 900 rows where the order numbers have multiple entries instead of single entries.
All the order numbers end "LO" and there all 8 digits long.
I wanted to know if its possible to use excel to look for all instances of "LO" in the column Order Numbers and delete the original Row and replace it with 3 rows with the same information.
Example:
Attached to this post!
I have 15 historic files each approx 35000 rows and I suspect there are more errors
I have a formula to find if the Symbol "!" is contained in the a cell. And i want it to input "Undercut Due to Trim Edge" if there is a "!" symbol and blank if there is not. What did I do wrong?
=IF(ISNUMBER(SEARCH("~!",AL57:AV57)),"UNDERCUT DUE TO TRIM Edge","")
I would like to "reverse concatenate" an address text string as follows: ....
View 9 Replies View RelatedI have used this code below to split a large excel file into multiple sheets from matching column data, but now I need to split it by a partial match (set number of characters from the beginning) from beginning of the column data.
For Example:
[Code]....
So with the code provided below using column 3 I would get 10 different sheets since none of the data in the column is identical. I want to modify the code (or come up with new code) so I can set the number of characters to compare from the beginning of the data in the set column and split into sheets based on that. So if I set it to the first 4 characters in column 3 I would receive only 5 sheets sheets: Safe, Fail, Dont, Poop, & 21-4.
What are the modifications or new code needed for this? I have searched for a bit with no luck, just keep finding code to check the full cell data for matches in a set column like this code I have:
SPLIT DATA FROM ONE SHEET TO MULTIPLE SHEETS
[Code] ......
I'm writing some VB in an Excel macro to search and replace a string of text.
The problem is that the string contains the " symbol, so when Access gets to that symbol it thinks that it's reached the end of the string.
e.g.
I want to replace "0519","863027" (including the quotes) with "0001","863027"
This is what the code looks like, but it obviously doesn't work :
I got a question regarding a what formula can i use to act like the Split method in programming. I would like if this can be done with a formula not programming cos i am a programmer Basically i got a column full of data in the following format:.dddd.ddddddd.ddddd. Now the number of the "d" can be random in between the dots. So i would like to be able to split the line by looking at the "." .There is one thing though that each line does have 3 or 4 "." characters.
View 3 Replies View RelatedI have previous been given the below macro from here that splits a string of text in one cell into groups of 30 and then puts them into several cells, works perfectly.
What I would like to incorpate now is the ability to overide the point at which it splits the text.
i.e. if the inputter puts a "|" (for example) in the original text, this will cause a split in the text and it will restart its 30 count from this point onwards.
Example of what I would like to achieve:
Cell A1 = I would like to change this string of text into groups of 30, where this appears | I would like it to start a new split of 30s from this point on wards and again if another one of these | appears in the text.
Cell B1 = I WOULD LIKE TO CHANGE THIS
Cell C1 = STRING OF TEXT INTO GROUPS OF
Cell D1 = 30, WHERE THIS APPEARS
[Code]....
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.
having a problem with a vlookup when the cell value contains ~
ie. John~Smith
it returns an #NA even when my lookup table has John~Smith in it.
I need a code for filtering the cell's content.
What i want to filter and delete is all/any text found following after the symbol ">" .
I want to return Y or N for each row where a certain word (lets say "Apple") appears anywhere in the reference cell. Therefore in the below example the first 3 rows contain apple (somewhere in that cell) so Y is returned. I can get it to work when apple isd the only thing in the cell but not if it appears with text before and/or after it.
ie:
A B
apple Y
apple orange Y
orange apple Y
pear N
grape N
So I have strings like the one below. What I'm looking for the text behind the ":" being, 17-SEP-2009, 000737-EWO, SRS and finally 002-YAO
Example string:
date: 17-SEP-2009, number:000737-EWO, detail: SRS, DD:002-YAO
My ...
Is there any way to split this string so I get Letter number number number in seperate cells?
|A533|B15A|C025|D205|E240|F036|G450|H166|P210|V123|W60C|X257|X264|Y108|Y233|Y288|Y426|Y428|Y432|
I am trying to do some automatic formating of a list of data. I can accomplish 90% of what I would like to do via a simple keystroke record macro. What I cannot do is shift the contents of Column B based on the contents of Column A. Column B will have to be shifted to the right 1, 2, 3 . . . cells. Below is an example. Column A symbols as it relates to number of cells to shift Column B
Symbol......Amount of shift
-[-]..........0
--( ).........1
--[-].........1
---( )........2
---[-]........2
----( ).......3
I have included a sample .xls showing the Original Data and the Desired Output
I'd like to adapt my macro so that it would insert a blank row after it detects the the first 16 characters of text as "'Closing Balance"; or it could even detect "'Closing" as the first 8 characters if it would be simpler
The Data Begins in Row 5 of Column A
My Current Macro is as below which I have adapted from another one I used
It is not working since it is detecting for the exact text "'Closing Balance" whereas the data registry would write "'Closing Balance as at 31/10/2009" , of which the "as at dd/mm/yyyy" portion would change every time a report is exported, but the first part "'Closing Balance" or even just the word "'Closing" will always be the same.
I have a sheet named: tool
In this sheet I have a long list of data that i would like to have sorted out on different worksheets that needs to be created by the macro. The deciding factor for the sorting are found colum B. In this colum there are different text strings. I would like for the macro to look at this text string and copy the entire line over to the respective work sheet. the work sheet needs to be named according to the text string in colom b.
Furthermore in the sheet named tool the first 3 lines are headings and i would like all of these transfered to each of the new sheets.
I am wanting to find within a cell the following pattern "ANNNN" - "A" meaning any alpha character A-Z and "N" meaning any numeric 0-9. The pattern ANNNN could be anywhere within the text. If the cell contains text with that pattern the text would be deposited in the adjacent cell. Lorem ipsum dolor sit A1234 amet, consectetuer adipiscing elit. Mauris tristique. Cum sociis
View 5 Replies View RelatedI'm trying to split the string in textbox 1 so i get the first name and the last name but this doesn't seem to work?
View 3 Replies View RelatedI need to split a string in all cells in one column (delimiter = ""). What I basically want is to get just the first word in the cell.
View 4 Replies View RelatedSay I have a string, "a test array." I want to split this into an array where each character is an element. I was thinking something like
Dim MyArray As Variant
Dim MyString As String
MyString = "a test array"
MyArray = Split(MyString, "", Len(MyString), vbTextCompare)
But this doesn't work because Split() returns the entire string when the delimiter is a zero-length string.
I'm stumbling upon something I just can't figure out. I thought I was being very clever using the Split() function, and it did all work beautifully for a while.
I've got a list sort of like this:
Helloworld
Hello of World
Hello1 of Planet
And I want to be able to run the following code.
For Each u In units
d_type_a = Split(u.Value, " of")
Debug.Print (d_type_a(0))
Select Case u.Value
Case "Hello"
stuff...
Case "Helloworld"
stuff...
Case "Hello1"
stuff...
End select
next
But it fails on the Debug.print with a "Subscript out of range, nr 9" error. The debug is just there because it doesn't want to work. The debug.print DOES out put "Helloworld" into the Immediate window but then still fails which sorta confuses me.
The best I can figure out is that it just doesn't quite like the string without an " of" in it.
I have a column with cells that look like: XYZ - JobABC - Area 1-A. I'd like to split the column into to and have it look like: XYZ <next column> Job ABC - Area 1-A
Normally I'd use the text to columns function and "-" as delimiter, however, there are other instances of "-" which I do not want separated. Note that the XYZ can vary in length and therefore fixed width will not work either. I have attached a small sample with the different variations that I could encounter.
How to make + symbol variable in the below formula?
This formula is in G25 and symbol is in F25 has + & - symbols
=(NOW()-E$592)+E614
How can I transform a text string into a formula. ='F1'!$C$2
This appears as a text and I want to convert it to a formula exactly like it's written. I made this formula like this because I need to get the value from 1000 sheets. So I use the formula to change the sheet number automatically.
But now I can not convert the text into a real formula.
I know that I could do it with a macro but I prefer to do it without using a macro.
What formula can I use to extract this string text.
I need to be able to extract the name after "and"
I would like to extract the name Lana from: