Close Window   
Free Ebook
Got an Excel Question?
Ask it in our Excel forum!
Subscribe for Free Excel tips & more!

How To Break Up A Text String?

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I've used CONCATENATE for a long time. Now I need to grab certain sections of strings between sets of delimeters. I was hoping to find a nice simple command to do this but may need to create a macro.

Is it macro or some text function that I'm overlooking?


View Answers     

Similar Excel Tutorials

Count the Number of Cells that Start or End with Specific Text in Excel
How to count cells that match text at the start or the end of a string in Excel. If you want a fuller explanation o ...
Count the Number of Cells that Contain Specific Text in Excel
How to count the number of cells that contain specific text within a spreadsheet in Excel. I'll show you how to cou ...
Stop a Macro from Running in Excel
How to stop a macro from running after it has started. This method uses the keyboard and has nothing to do with th ...
Break out of or Exit Different Types of Loops in VBA Macros in Excel
How to Exit, End, or Break out of loops in Excel. This tutorial includes breaking out of While, Do, and For loops. ...

Helpful Excel Macros

Extract the First Word from a Cell in Excel - User Defined Delimiter Text Extraction - UDF
- This free Excel UDF (user defined function) returns the first word from a cell in Excel. This extracts the first word f
Extract the Last Word from a Cell in Excel - User Defined Delimiter Text Extraction - UDF
- This UDF (user defined function) extracts the last word or characters from a cell in Excel. This is done by finding the
Filter Data in Excel to Display Results that Contain 1 of 2 Possible Values - AutoFilter
- This Excel macro filters data in Excel to display results that contain 1 of 2 possible values. This macro uses the xlor
Enter Text/Characters with a Double-Click of the Mouse
- This macro allows you to enter any text or number, that you specify, in a cell on which you double-click. In the exampl
Create a Line Chart with a Macro in Excel
- Create a line chart in Excel with this macro. This allows you to quickly and easily create line charts and graphs from

Similar Topics

Wondering how I can go about this.

Have a long list of bond names (full text string I want) and another list that is compiled with essentially the same data, just more of it and unsorted. Is there a formula that is capable of matching the 3/4 text strings I have to the full text strings I have? the 3/4's are not "closest match" they should link up exactly to a full string in other cells.

For example, A1:A2800 = full text strings (columns b, c, d have other data) E1:E500 has the 3/4 text strings that I want to reconcile with the full ones. Want to lookup the full text string, find the 3/4 string tha matches and then have the corresponding data to the 3/4 strings pulled out.

let me know if you need more clarity.

Thanks in advance.


The goal of my worksheet was to find the most frequently occurring text strings in a long list. This was accomplished using the combination of formulas in Sales 1 F7 and F8. F7 has a MODE formula to find the most frequently occurring strings and then F8 and below contain an array formula to find the rest.

The problem occurs when the text strings contain a tilde at the beginning of the string. In Sales 2, I replaced all the "Bill" strings with "~Bill" strings and you can see the results change considerably.

So - without a simple text replacement of the "~" - how can you make this function/formula work and return the correct results as in Sales 1?


I have been struggling with this for a few hours and have exhausted every avenue i can think of.
I have a text file with several hundred strings of text. Each string is of varying length. I wish to extract the last four numbers from each string. The numbers are separated by spaces and vary between 0 and 9999, so i cannot do a simple RIGHT function.
What i have tried is the following;
I found quite a nice macro that reverses the text in a string. I use this on each line, thereby putting the numbers i want at the start of the string, but backwards. I then use text to columns, using space as the delimiter. I then use the same reverse function as above on each of the fields i require.
If any of the numbers end in 0, after i reverse the string and then delimit it the zero is lost because it is treated as a leading zero. I have tried formatting the fields both before and after the delimitation, either as text fields or by adding in zeros with a custom format and nothing works.
Does anyone know of a way to keep leading zeros after a string is delimited? Any other suggestions as to how to solve my problem are more than welcome too!

Good day.

In the first column, first position I have a string of data identifying the first amino acid in a protein. Each subsequent row beneath it has identifying information for the next amino acid.

In the adjacent column I have converted that string of data to the IUPAC-IUB one-letter abbreviation for each amino acid (I used VLOOKUP and a table on another sheet for that). I have set the cell format in that column to text.

The result looks something like this:

[string] [letter]
[string] [letter]
[string] [letter]

I desperately want to concatenate all of the letters in column B into a single text string that represents the protein's sequence. However, as the particular protein I am working with is 1620 amino acids long, a representative length of my sample set, it exceeds the 255 cell limit of the concatenate function by a considerable margin.

The question I submit is this: does anyone know a way to concatenate more than 255 text strings? Something simple would be preferable.

Fellow Forum Members,
I'm crossing data over from a PDF file. Sometimes I get a 10 line Paragraph end up horizontally on cell A1, A2, and A3 and other paragraphs sometimes end up vertically on cell C1, D1, E1. I need all the text for the paragraph to actually occupy a single cell such as cell A1 and not spread across 3 cells. I tried using the merge cell command and the drawback to this option is that it drops text.

Can anyone out there help me develop a macro that does the following:

I highlight cells A1, A2, A3 and A4 then click a macro command button and then the text inside these cells join together as a single text string inside cell A1. The order of how the text strings join together needs to be determined by how the cell location increments by number or letter. For example, A1 text is first, followed by A2, A3, and A4. Or if it's A1, B1, C1 and D1 the macro will arrange the text in cell A1, followed by cell B1, C1, and D1. Lastly, it only works on what I have selected.

In short, is such a Macro possible? Currently, I have to manually cut and paste all the text strings into one cell and this is a lot of work. I have tried concatenating but this falls short because I need a more automated solution.

Any help will be greatly appreciated. Thanks.

Dear all,

I have a task that require the concatenate the text of two columns. I searched in the forum, some of them will delete the original column.

I just would like to have a simple macro for concatenate the text of two columns. For example,

Column E (product)
Column F (group).
I have just want to insert new column G with concatenate text between two columns.

I believe it should use macro-offset.
Would you mind provide the macro that I can change the column of concatenate text easily? for example, change concatenate E and F to concatenate G and H. I am not good at macro a lot, but I believe I can change the columns in the code....

If you can reply me, I will appreciate for your help.

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

Hi there! I am new to vba and I need to create a macro asap that can extract a text string from a text file. The text string lies between 2 other text strings that repetitively occur in the text file. For those of you that are good at vba, this won't be hard for you (unlike me) I am attaching the file below:

so from this text, i need to extract the first name and last name, industry and location in different columns. You might be able to see that there are differnt first names between the text ( "firstName":" ) and ( ","connectionCount ). Similarly different last names between ( "lastName":" ) and ( ","linkAuto ). Then there are different industries after ( ","fmt_headline":" ) and different locations right after industry. Even if the macro copies down duplicates its fine because it will be one extra step to remove dupes. I am attaching the file and you can find what I'm looking for by searching for these strings in the text.

I am macro illiterate and in advance I want to deeply appreciate whoever will get this working for me. you guys help out novice people like us and its a great deal! Thank you so so much!! Hats off to your brains!


I think I have a fairly simple problem but can't find the solution. I have a column full to text strings. Each of the text strings has multiple 'substrings' of text seperated by a ";" that I would like displayed on a new line in the same cell. I know I can manually add a line break with ALT+Enter. How do I write a macro to search the entire column and add a line break (I think its "chr(10)") after each ";"

In Cell A1:
Roses are red; Violets are blue; I got nothing;

In Cell A2

Another fake line; why aren't I creative?

In Cell A1
Roses are red
Violets are blue
I got nothing

In cell A2
Anotehr fake line
why aren't I creative?

Thanks in advance!! (note, I don't care if the ";" is replaced by the line break or if the line break is added afterwards...


I am using vlookup function for a few hundred items, which are pure text strings. For few items the formula doesnt work and throws "#value"error. I found out that the error occurs only when the length of text string exceeds 255 and for a given text string if i reduce the length to 254, formula works.

Is there such a character limit for vlookup function? is there any ways to get the formula working for such long strings?? or is there any other ways to lookup for these long text??

plzz hhelp me.....

thanks in advance....

I need to find a Text, set in cell e.g. ("A1"), within various strings in a certain Range, e.g. ("B1:K20") /there are different strings by long in cells in the Range, but always contain one or even more times text set by A1/, and Replace this Text with the other Text, set by cell, e.g ("A2").
I would like to do so using VBA macro, despite the fact that I could simply select the range and use Ctrl+H with Find Text and Replace Text. Many thanks.

Recently I had to copy data from an adobe acrobat Report, and paste it into Excel. I am now trying to either use (data to text) columns, use (Left) and (Right) formulas to break apart my text string. The problem is, the data is not lined up properly, thus allowing me to use a simple data to text column separator.

What is the best way to separate a long series of text? Is there an extract function that may be used?

An example of my string of text looks like this...
, 108442 99 11:57 11:57 00:00 JEREMY 02/VA 11 14:39 14:39 00:00 MICHAEL 02:42 5.00 02/23 02/23

Please advise if there is any possibility to do this?


I'm using the concatenate function to join two bits of text - but I want a
space between, and the function joins them together without a space. How
can I insert a space into the resulting text please? (It seems to me that
we would want such a space in ANY two text strings which we join together
with concatenate - wouldn't we?? Why doesn't it put one in by default?!)



I am looking for a method that will allow me to click a command button and the text that is on that button will then appear in the last blank cell in a certain column. The only part I am stuck on is how to make the text on the command button insert when clicked rather than creating a macro for each text string in the code. I can do it the long way and make a macro for each button with the text in the macro itself, but the text on the buttons will change on occasion and it would save me a lot of editing as there are between 70 and 100 buttons. Possibly a simple solution but I have not found anything online or with my own attempts at writing it myself.

Thanks in advance and thanks for reading!


I have been searching the forum how to find a string inside another and thats no problem. The thing I can't seem to find is how to Colour the cells where the text has been found. So, to the problem:
I have several text strings, one string in every cell from B2400 to B3400 let's call it T1, then I have text strings from cell B1 to B2400 let it be T2, those strings are longer and include more text then the strings in T1.
Now what I would like is to find and mark every row where T1 is a part of the text in T2.
Guess this is an easy task, but since I have never worked with VB I'm a bit lost.
Would be super great if someone could show how to easily solve this.


I am looking for a formula that will locate a text value within a array of text strings and then return the value on a corresponding row. Here is an example:

I have 3 columns, A,B,C

In column A I have 4000 rows of data and each row has a text string. These text strings are of varying length and are made up of different code combinations, and each code is sepreated by a column. So, A2 is 0359AGG669,5689HHG6526,33432V3,232VVD and A3 is 2231,456TTR4445 and A4 is 266215162,2656V3,0323V2,6262625454. As you can see these vary in length and content.

In column B I have IDs that correspond to the values in Column A. So, B2 = 1511233 and it corresponds to the text string in A2. So, each value in Column A has a corresponsing ID in Column B.

In Column C I have 439 rows of text strings. Each one of these are found within one of the text strings in Column A. So, in C2 I have 1245626 and this value is located in one of the text strings in column A.

I need the formula find each individual value in Column C and then tell me every instance where the value exists in column A, by returning the corresponding value in column B. So, there is one value in each row in column C and that value might exist in multiple text strings in Column A.

I am attaching an example.

Hey folks,

I have a series of strings for which I want to test each one individually and find out whether it appears within a larger text.

Now, for a normal text I would just do (and have done) something akin to:
=SEARCH(C1,'Text to Test'!$A$1)
and make a simple column out of it.

But what do I do if the text is too long to fit within a cell (i.e., excel gives me an error beep when I try to paste it)? Is there any way to have Excel compare the string in C1 to the text of a Word or .txt file?


I want to create a string of text to use as a variable. problem is one of the strings i want to combine is already a text string. i want to dot his:

LastLocationxxx = concatenate("lastlocation" & VesselNameActive)

where vesselnameactive is already a textstring. i am wanting a final product of "lastlocationGI40F", if vessnameactive was "GI40F". how come concatenate does not work??

I am trying to put together an excel macro to compare two text strings.
The first column of data that may have errors or missing characters, while the second column has the text string that has no errors for comparison.

The macro compares the two strings in columns B (Entry) and C (Corrected),
returns the string in column B to Column D (Highlight) and highlights
in blue the characters in column B that are different to those in column C.
The strings are sentences.

Can I just do this with a little judicious use of the EXACT function and conditional formatting?

Hey all!

I have a table filled with text strings and I would like to write a macro that locates a particular word in each cell and counts the number of occurrences of that word in all of the cells. I can probably separate all the words in all the cells using the "text to columns" feature in excel and then create a double loop tp check each word to see if it is the word I am looking for.
However, I know that will have a ridiculously long run time.
Is there any way to do it faster?

1 Moses went to the supermarket today
2 John bought flowers today for his wife
3 Yesterday I cried

The word I need to find is "today"

so the output would be:

1 Today 2



Sorry about this but I am a complete novice and was wondering if there is any way of achieving the following:

I have a number of rows, 22 columns long containing text strings. I need to search each column of each row to see if the text string is contained within a text string of another cell.

If any of the text strings within the first two columns match any of the text within the single cell it should return a true result but if the text string also contains any of the text strings from the remaining 20 columns it must return a false result.

An example being:

The single text string is 'CATASTROPHE'
There are 22 columns of smaller text strings (the longest being 6 char) and in the first column is the text string 'CAT'. There is also a text string in the 8th column of 'STROP'.

This would need to return a false result but if none of the remaining columns after 'CAT' matched any of the lead text string the result must be true.

Is there any way of achieving this? Any help would be much appreciated.

If you need any more info or sample data please let me know.

After much searching the forums I can't find exactly what I need (nor a version that I'm skilled enough to modify).

I need a macro that will look for a specific text string in the tab names of the workbook and replace it with a new specified text string (leaving the rest of the existing tab names). In other words, a simple find/replace but applied to all tab names in the workbook rather than cells. Ideally, I'd like it to pop up something and ask for the text to find and the text to replace it with, so I don't have to edit the macro itself each time I want to use it, but editing the macro each time is fine. Either way will be wonderful.



I try to concatenate some 5 text strings and I would like to have a carriage return after each text string (ALT ENTER).
How can I achieve this using =CONCATENATE(......;......;......;......;......)?


Hello there, I am wondering if someone can help me. I have a list of text strings that I'd like to convert to a list of numbers, such that each letter equates to:

A = 10
B = 11
C = 12, etc.

At this point I'm using the MID function to pull specific letters out of a text string, and then the CODE function to convert them to a number.

So =CODE(MID(A1,1,1))-87 = 10, where A1 = "ADE"

So far so good, but here's my issue: I don't know how to tell Excel that the number of characters varies in the text string I want to convert.

Optimally, I'd like to convert ADE to 101415, then add on six zeroes to the end of that number to allow for the fact that other text strings may have up to a total of six letters.

I'm doing all this so I can rank a list of random text strings alphabetically. I want to take the number assigned to each text string, then I can use RANK to order those strings (I think!).

Anyway, thank you so much if anyone can help.

Take care,



I am creating a report in excel which has large strings of text in certain cells (that are imported from a master spreadsheet via macro) which are longer than the maximum for the wrap text function. (And by the way the cells are merged in case that makes a difference)

Does anyone know of an alternative way to make sure all the text is visible other than by breaking the text up into smaller blocks in several cells?

Many thanks,