Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Copy Text After Character

I have a column list that I need to seperate. The Text has both a title and location separated by an = sign. The cells look like this...

BlahBlahBlah = Location1

I would like to take the text after the = sign and place it in the next column so that all that is in it is...


The Location varies so the RIGHT function won't work.

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Split Text After Specified Character
You know how you can combine different cells and concatenate their values? Can you reverse that? For Example, I have this value "Yambao, Mikhail P." and I want to break them apart so I can use "Yambao" as a reference value to fill up other information concerning that name.

View Replies!   View Related
Reading One Character Of Text
The following code reads text one character at a time, and each character is determined either to be a blank space or to be any other character. If it's determined to be any other character, then the character is added to the active cell. If it's determined to be a blank space, then the active cell becomes the cell one column over. The idea is to have different words written in columns next to each other.

The sample text file I'm reading from reads: "text file". The file has only those two words. One space between them. No space before the first word, and no space after the second word. The following code compiles and runs. BUT the result is that the word "text" is in column k, not A. And the word "file" is in column J, not b.

View Replies!   View Related
Is Nth Character Text Or Number
Cell B23:687968307=ISTEXT(MID(RIGHT(B23,3),1,1))TRUE
Cell B24:C8796B07=ISTEXT(MID(RIGHT(B24,3),1,1))TRUE

I'm trying to test whether the third last character (3 and B) is a text or not.

As you can see, the reslts are both TRUE, but I'm expecting to see that the first one should be FALSE, as '3' is a number right?

Likewise, if I replace the formula with ISNUMBER instead of ISTEXT, the results are both FALSE, but shouldn't the first one be TRUE, again, because the '3' is a number?

Have I missed something in the formula, or is there a better way of expressing this formula?

View Replies!   View Related
Extract Text After Last Specified Character
I have a cell in my worksheet with a fully qualified filename like '

I would like to extract only the Obviously, the number of characters is going to vary based on the filename. The find and search functions appear to help locate the cell but not the substring in the text. The right and left appear to work based on number of characters and in my case these are varying. Also, there does not appear to be any function that can do a search a string from right to left.

View Replies!   View Related
First Character In Cell Is Text Then Delete Row
In a column I have cells where the first character is numeric and the rest text and also cells where the first character is text.

I want to delete the rows where the first character is text.

View Replies!   View Related
Remove First And Last Character From Text File
I'm trying to write to text file a HTML page that is in string variable sFullPage.

So far my code is like this:

Sub wrtHTML()
Dim sFName As String ' Path and name of text file
Dim iFNumber As Integer ' File Number

sFName = "c: est.html"

'Get an unused file number
iFNumber = FreeFile

'Create new file or overwrite existing file
Open sFName For Output As #iFNumber

'Write data to file....

How do I remove those first and last two marks (a double quote on each side + square mark from the end)? Do I use somehow wrong data types or wrong printing methods?

View Replies!   View Related
Moving Text After Particular Character To Next Column
At this point, I have a list in the first column that looks something like the following:.......

First, I want to delete any cells that have two or more '' characters and delete any cells that do not have any text after the first '' character. Then I want to take the text before the character and put it in column B, deleting the ''. Once I have done these things, my original post and this one can be marked solved.

View Replies!   View Related
Function: Return Text After Specified Character
I am trying to build a formula that return what is after ":" For example, in a cell, I have NASDAQ:MSFT and I want another cell to return MSFT. I tried to use the function FIND combined with a LEFT or RIGHT but I could not have it work.

View Replies!   View Related
Find Specific Character - Copy Into Next Row & Continue
I have an excel file in which the cell has more than 3000 entries, like following. I would like to copy the contents before ";" to next row & continue.



Answer should be in excel:

I do not know how can I achieve this through "Macro". I would like to detect ; in the cell & copy the contents before/after it in the next rows & continue till the last figure.

View Replies!   View Related
VBA Function For Finding A Character In A Text String
Is there a function in VBA that is similar to either the FIND function or SEARCH function in Excel? The arguments for the FIND function in Excel are FIND(find_text, within_text, [position]).

I have a text string in VBA ("$A1:$D$13") that I want to be able to identify the first "$" and then later the ":". I'm getting tripped up on the 3rd line of code. Thanks a million.

Sub page_set_print_area()
ActiveSheet.PageSetup.PrintArea = "$A$1:$D$13"
x = ActiveSheet.PageSetup.PrintArea
Position$ = Search("$", x, 0)
End Sub

View Replies!   View Related
Retrieve A Character From A Text String In A Column?
i want to extract a value from a column into another column in excel.
this first column contains text and i want to extract a value that in located between the characters 'FOR' and 'MTHS'.
how do i do that?

the situation is the same for all the rows; the value that i want is just located between these characters.
is there a simpler way other than using the MID functn? because everytime i use it i need to count the no. of characters and its quite difficult to keep up with the numbers.

View Replies!   View Related
Open A Wrapped Text Cell To First Character
I have to edit text cells (lots of them) containing approximatley 40 lines of text (screen width) - generally the edit will be done on lines 1-5

When I click on a cell, Excel is opening approximately half-way thru the text

I have to Ctrl Home to get to first line on each cell.

Is there a way I can set Excel 7 so that it opens the cell on the first line, first character of the text?

View Replies!   View Related
Text Parsing, Find String After : Character
I am reading lines from a textfile. Each line in the textfile has the identical format: textstring1:textstring2. The two strings are always separated by the : character. I have the code to get textstring1, but because I'm a rookie, I can't figure out how to get textstring2. See the code in bold, this is the line I need to get textstring2.

View Replies!   View Related
Delete Unrecognized Character In Text File
I am trying to read a text file into a variable with VBA. The files I try to read usually have a bunch of unrecognized characters in them and I can't seem to read through them. I am looking for some code that will delete all unrecognized characters until it finds a string I specify.

Example of file

 J Y D Y F Y ZE Y B Z 6 Z

My code works fine if I manually delete all this stuff before GOOD DATA, but wont work if I dont. My "responseposition" is always 0 unless I delete all the nonsense.

Sub findvalues()

Dim strText As String

strText = GetFileContent("C:currentday.gdbm")

cardstring = "GOOD DATA"
responsePosition = InStr(1, strText, cardstring, vbTextCompare)

MsgBox (responsePosition)

End Sub

View Replies!   View Related
Remove Text Character From Cells With Numbers
I want to change the character ~ with . in order to be able to make them numeric values to be feeded to other functions. But REPLACE seems not doing the job so I've been checking out other options such as seperating after and before the character ~. Details are below. I've been trying to use this formula to extract values from a delimited database which I open with excel. The formula that has brought me close is =IF(ISNUMBER(E51)=FALSE,LEFT(E51,LEN(E51)- FIND("~",E51)),E51)


When I import the database, the figures above have originals as 14010.00000 & 3210.00000 but transfer to excel as above. As far as I have observed 9 character spaces are displayed & the DOT transfers to ~ for some reason. I need the LEFT section of the ~

View Replies!   View Related
Determine Left Most Character In Cell Text
Could someone tell me if the following formula is valid?

=AND(F15="R*", I15>0)

I want to know if F15 contains 'R' or 'RW' but using a wildcard always returns false whether this correct or not.

View Replies!   View Related
Break A Long Text String Into 10 Character Strings
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 Replies!   View Related
Dermine Value Of Cell Number Preceeded With Text Character
I've tried and tried and I con not get it right. In A1 I have a validation, it onlu allows to enter a 6-digit number between 100000-899999 or the same numbers with the letter I in front, that is I100000-I899999. Now I need a macro to check that cell F2 is not empty if the value in Cell A1 is above 299999. And this should also apply if there is an I in front. The function for the validation is:


The code I have is:

If Range("A" & i).Value > "299999" Then
If Range("F" & i).Value = "" Then
MsgBox "Indicator missing on row " & i
Application.Goto .Range("F" & i)
Exit Sub
End If
End If

It works wor the nubers without the I but it demands a vlaue in F on all I numbers and I only want it for >I299999.

View Replies!   View Related
Sort Text By Character Length, Insert Spaces, Merge Cells
I have multiple columns with many rows of unique text in each. Here is an example of what the 1st few rows of column a and b might look like:


I would like to be able to sort the rows by the character length in a column. So, it would look like this (if sorted by 1st column):



View Replies!   View Related
Append All Digits Before Character To All Numbers Between The Same Character
I have got a list of numeric abbreviations, for instance 10739011/21/31/41. What it should really display are the numbers 10739011, 10739021, 10739031 and 10739041 (the first six figures stay the same). All the numbers in my list are 8 figures long. I want to change the list from the list seperated by the backward slash to the complete numbers. I have uploaded an example of the list with backward slash between the numbers. Is there a way that Excel can automatically change these numbers to the full numbers?

Because all the numbers are 8 figures long, I thought the first 6 figures of the 1st number can be copied and those 6 figures pasted before the other two figures after the backslash. Auto Merged Post Until 24 Hrs Passes;sorry, pressed OK too quickly. The problem is that there are sometimes 4 numbers in the cell, sometimes 6 and once three. I would like Excel to complete all the numbers in the cell and then move on to the cell underneath it and so on. Also, I would like each number to have it's own cell.

View Replies!   View Related
Look For Character In Cell And Insert New Character - For More Than One
I have a cell which will contain SER01+SER02+SER03

and what i need it to contain is [SER01]+[SER02]+[SER03]

and shocker is i've got this to work for the first instance but not the other two

code as below... be grateful for your help

Sub measure1()
Dim list As String, pos As Integer, refl As String, refr As String, newlist As String
list = Cells(1472, 16).Value
pos = InStr(list, "+")
refl = Left(list, pos - 1)
refr = Right(list, pos + 1)
newlist = "[" & refl & "]"
Cells(1472, 17) = newlist
End Sub

View Replies!   View Related
Add Character After 1st Character Of Cells
Need a formula/code that will determine what the corrected part number should be (insert dashes if they are missing) by comparing to other values in the list.

Original A1:A5 = {452, a-bc, 123, 4-52, abc}
Corrected C1:C5 = {4-52, a-bc, 123, 4-52, a-bc}

I can do this using an intermediate working column, but can this be done all in one formula, or via VBA?

My formulaic solution is thus: .....

View Replies!   View Related
Looping, Open Text File, Copy Text, Close Text File
I have an existing spreadsheet with a column of strings (actually VIN numbers). These numbers correllate to a bunch of text files, that can exist in one of three folders (UsernameDesktop1, 2, or 3) on my desktop. What I need the macro to do is:

1) get the filename from A2 (A1 is a heading row)
2) Find the appropriate text file in one of the three folders
3) Put the folder name into I2
4) Scan the text file for some strings, and copy some data that follows those strings into J2:O2 (I can handle programming this)
5) Close the text file
6) repeat above for the remainder of filenames (about 1800 files)

View Replies!   View Related
Copy Text From Html Text Box
Here is my dilemma: I am opening an HTML file with Excel. There are text boxes that appear in excel from the HTML file with text in them. I would like to write a macro to copy the text from each text box and paste it into a cell. I have attached the excel file with the html text boxes in question.

View Replies!   View Related
Copy Text Only If It = Certain Text
I want to copy text to a new spreadsheet only if column A = "47"

The problem is, if I drag the if = formula down in a new spreadsheet, the data will have blank cells and will go down thousands of rows. I want the first "47" to be in the first row, the second "47" to be in the second row of the new spreadsheet and so on. Can this be done?

View Replies!   View Related
Copy Number From Text
I'm running Excel 2003. I have a workbook and in Column L I have a text description that includes one or more 5 or 8 digit catalog id's. What I want to do, using a function or vba, is to copy the id and paste it into Column M.

In cases where there are multiple id's I would want all of them to be entered into the same cell.




All of the 8 digit id's have either 2 or 3 leading zereos that must be retained.

I've attached a sample spreadsheet. Thanks in advance for any pointers and/or assistance.

View Replies!   View Related
Copy Text From One Sheet To Another Using VBA
I am not familiar with using VBA and believe this could solve my problem, I would be glad of any assistance.

I am trying to copy text from sheet 1 of my workbook onto sheet two of my workbook, whilst consolidating into a nice neat list omitting any blank cells.

The text appears on multiple tables each table has 10 rows throughout worksheet 1

A1 = Good
A2 = Bad
" "
A10 = Fantastic

A15 = Good
A2 = Bad
A10 = Fantastic

I hope this makes sense, I do have a formula that will do this =IF(ROW()-ROW($E$500:$E$509)+1>ROWS($K$35:$K$44)-COUNTBLANK($K$35:$K$44),"",INDIRECT(ADDRESS(SMALL((IF($K$35:$K$44<>"",ROW($K$35:$K$44),ROW()+ROWS($K $35:$K$44))),ROW()-ROW($E$500:$E$509)+1),COLUMN($K$35:$K$44),4))) however I have multiple tables and the formula greatly reduces the speed at which the sheet is working.

View Replies!   View Related
Text That I Use To Copy AL If AJ Is Not Blank
Below is the text that I use to copy AL if AJ is not blank:

If Range("AJ2").Value <> "" Then

What I want it to do is copy the formula out of AL2 down to as many cells necessary to populate column AL as long as AJ is not blank. If AJ is not blank I want it copy AL2 down as much as needed to fill column AL. I currently have it running to AL65000 but don't want to leave the formula in that many cells. If there is nothing in AJ I don't want anything to be in column AL.

End If

End Sub

View Replies!   View Related
Copy Part Of Text
My file is included with alot of cell in the following format:
aaaa. bbbbbbb (ccccccc)

I want to copy the first part (aaaa) into column A and to copy the second part (bbbbbbb) in column B. Both part can vary in length: in this case their length were 4 and 7 but basically they can have any length. How can i do this. See also the attached file.

View Replies!   View Related
Copy Picture As Text
i have this picture of a list of chores..its about 150 different chores long

i can type all of it in..that would take a long time..

but anyway i was wondering if there was a way to take the picture and turn it into words..


A1 Sweep Kitchen Floor
A2 Mop Kitchen Floor
A3 Empty Dishwasher
A4 Fill Dishwasher

View Replies!   View Related
Copy Web Text / Source?
The problem is, these identifiers are in no discernable or predictable pattern. I cannot open the page directly in excel, nor can I use the Import Data from Web function (2007) ... results are simply a blank page.

What I thought I could do, then, is automate the procedure that obtains the source code, which I can parse and look for the current date. Once I have the line with the current date, I can extract the unique identifier, then paste it back into a string and resubmit to the browser.

I just can't figure out how to get to the source code... anybody out there have a way to get to it? Since this is going to ultimately be distributed to 20 or so analysts in different countries, I don't think I can use other tools (like the HTML Extractor from Iconico).

View Replies!   View Related
Copy Certain Text And Its Row Into A New Spreadsheet
I am trying to make a macro where when you click the Export button Macro it just takes anything that has a Yes in Column A2:A100

View Replies!   View Related
Copy And Paste If Not Certain Text
On sheet1 have multiple groups of cells with drop down lists all with the default word "None" selected. After the user has gone through and changed some of these menus to other values in the lists, i am looking to copy the text only in the cells that have been changed to certain locations on sheet2. See attached spreadsheet for a better idea. If any additional info is needed.

View Replies!   View Related
Copy Text To Certain Heading
method to copy some data to a variable place in the worksheet.

Column A has team names with data in columns B,C,D and E.

I would like to group together the data for each teamname - so have the team names in Cells H5,H10,H15 and H20.

I have attached some sample data

View Replies!   View Related
Copy Text From 1 Sheet To Another
clicking the platform button on sheet 2 runs a module that plots trains waiting in platforms 1-15 the value are got from sheet 1 columns

a = arrive
b = depart
c = platform

ideally i now need these shaded cells to show the departure code of the train they represent and i need every cell of the train to show the code (eg all 60 cells if the train stays for 1 hour). this is because some trains arrive as 1 but detach and depart as 2 or more trains. this would show where 1 train leaves but another part remains to leave later under another headcode, the codes i need placing over the shaded cells are the code in column I sheet 1 (ideally written in the invert colour of the shaded cell so they can be read easily) as an example of what i'm after i've manually added the code for 1 train in black on sheet 2 for the 1st train in the list

View Replies!   View Related
Copy Numbers But Paste As Text
We have a template sheet we use which has a sheet formatted as 'text' - it msu remain this way for another purpose.

We wish to copy information from other sheets/places and paste into the template sheet - but have it as text (with green triangle on it)

I have tried copy and pasting (the sheet is already formatted as text.
I have tried copy and paste special as values but to no avail.

(I have attached a small example which shows the template, data to past and end result of what I require - the redtab sheets are attempts to paste!

I know there is a really simple solution but I cannot for the life of me see it!!!!!!

Please help! Auto Merged Post;I kept searching and have found a way to do it on 1 column...
(Data/Text to columns....text)
- but how do you do it over a sheet that contains say 120 columns (i.e.: en masse)..???

View Replies!   View Related
Copy Button Text Into A Macro
How do I copy the button text and use it in a macro?

I have a worksheet that tracks several employees' work. I copy the rows with their work into a sheet with their name. Right now I have eight macros with the macros referencing their names. I would like to have one macro and just change the name depending on the button I pick.

View Replies!   View Related
Automatically Copy Only BOLD Text
I have a spreadsheet with a pivot table in it. Column "G" has all of the data in it I need to abbreviate, problem is, I only want the values in BOLD face, and there are 3-5 rows seperating each boldfaced item. I would have to manually copy-paste each bold value into a new, 'clean' column ( column "I", or in a new sheet/workbook, it doesn't matter), and lather-rinse-repeat for the next 700+ rows. It sucks. No, it really does. I stumbled upon this website which is seemingly the utopia for excel macro and programming problems, so is there anyone out there that might take the time to walk me through it...?

View Replies!   View Related
Copy The Text From Variable To Clipboard
There is an text string and I need to copy it to Windows clipboard to have a chance to use it later anywhere by Ctrl+V.

Simple, isn't true? But how to do it?

View Replies!   View Related
Copy Cell, Paste Text
If a cell contains the formula =b1 and the result shows as zero;

Is there any way of getting the text "=b1" in a cell next to it instead of zero?

View Replies!   View Related
Locate Text & Copy All Cells Above
1. Find a Text
2. Select the cell
3. Select the row and columns above the data we found
4. Copy it

View Replies!   View Related
Copy Selected Text From TextBox
I have a userform in which I wish to use custom buttons for several commands. My userform users aren't going to be very computer skilled and may not know the CTRL+C or CTRL+V command so I am hoping to put in small image buttons representing each command to be able to copy (and paste if necessary) their selection in a TextBox on a MultiPage'd UserForm.

Is there any way of doing this? I've had a look through the forums and seen several examples of how it can be possible to copy and paste but not in the method I'd prefer to have it on this UserForm.

View Replies!   View Related
Find Text And Copy To New Workbook
I want create a macro which will find a text "9:55:00" in the cell of column A and if found copy that entire row in a new workbook which i have opened. the text "9:55:00" will be there every workbook i will open. Once the row is copied in the new workbook, when i again do the second find the row must be copied below the first find in the new workbook.

View Replies!   View Related
Copy Columns With Specific Text In Header Row
The column header row for a string; if the header contains one of two possible strings then copy the entire column. I've tried the following code but it's not working.

Select Case oColHeader
Case InStr(oColHeader, "how much", 1) > 0
Case InStr(oColHeader, "level of sat", 1) > 0
End Select

View Replies!   View Related
If Text Is Found On Two Sheets, Copy Entire Row
I have one master worksheet with 1000 ID's. I also have 10 other spreadsheets with 1000 ID's. I need my macro to use the master worksheet and identify where the IDs are located among the 10 spreadsheets. If there is a match, then my macro should copy the entire row and place it in an output file (another spreadsheet).

The following code only tests the master worksheet and three other sheets (one being null/error is ID not found).

Sub Macro5()
' Macro5 Macro
Dim Raw, Mix1, Mix2, error As Excel.Worksheet
Set Raw = Sheets("RAW_DATA")
Set Mix1 = Sheets("Mix1")
Set Mix2 = Sheets("Mix2")
Set error = Sheets("error")
For x = 1 To 1000
For y = 1 To 1000
If Raw.Cells(x, 1) = Mix1.Cells(y, 1) Then.................

View Replies!   View Related
How To Find A Specific Text And Copy This Entire Row
I tried to writer my code myself but I have a long way to go. Here is what I must do for my case:

I want a macro to find the row that include a specific text (For example "SMSC") in a range (A1:A100) then copy this entire row below the cell which has the value "OTHERS". That is, if there are 10 pieces of "SMSC" so these rows including "SMSC" should be listed below the cell "OTHERS"

View Replies!   View Related
Find Text And Copy Found Range
I have some EXCEL files, titled as "AAA", "BBB", "CCC", so on... Calculated values are placed in Column Z for each file. In order for me to export the files to another software, I would need to create another new EXCEL file (I call it MASS) and place all the calculated values in that new file. In that new file, the titles have to be on Column A and the calculated values on Column B.

For example, File "AAA" has 120 calculated values. In MASS, the word "AAA" has to appear on Column A a number of 120 times and the 120 calculated values are then placed on Column B. Each value must correspond to the word "AAA". The same procedure goes for "BBB", in which the word "BBB" is placed on Column A after the end of "AAA". What I want to achieve is by using a macro that will look up the title of the Active Worksheet and then scan Column A in MASS. If the title matches whatever is in Column A, then the macro will paste the corresponding calculated values onto Column B automatically.

For example, for file "GGG", the macro will look up the word "GGG", scan through Column A in MASS for the start of the word "GGG" and paste the calculated values at the start of Column B. Could I have an idea of how might the macro looks like?

View Replies!   View Related
Maco That Will Copy Text Down And Then Stop At Specific Row
What I'd like to do is copy text down in column A but when a cell in column C says something like 'end of data' I'd like the copy to pick up the next row in Column A and copy down until 'end of data' and so on.

Or maybe copy text down in Column A until the text changes in col A.

View Replies!   View Related
Formula Would Copy Over The Format And Change The Text
i'm having trouble working coming up with a formula. I have a list of people and if i put a x next to their name, i want their name to cross outs. the IF formula does not copy over the format (strikethrough, italics). my question is, what formula would copy this over and change the text?

View Replies!   View Related
Form Picker And Copy Text To Another Sheet
I would like to create a spreadsheet that when a selected number of cells are set to "yes" then a lookup to a small table should then copy 3 columns of text at the side of that table, if the criteria is met with a "1". If either f2, f3 or F4 = "Yes" then look up A9:c16 and if there is a "1" in respective column then copy all text to the right in columns D, E & F to "result" work sheet. I am trying to create auto copy of selected data without the use of a filter, by pressing a cell / button.

View Replies!   View Related
Copyright 2005-08, All rights reserved