Email:      Pass:    Pass?
Close Window   
TE
Subscribe for Email Updates!
Excel tips, help, and more!
E-mail:


Advertisements


Free Excel Forum

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?

Thanks!

View Answers     

Similar Excel Video Tutorials

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







Hello everybody. I know that the Concatenate function joins several text strings into one text string, and I have used that funtion many times. Now I need a way to do the opposite. I need to take one text string and break it into several text strings, each value getting one cell. Thank you for any help you provide.


I have a single cell which uses an "if" function with nested "concatenate" functions to produce a string of text. I need to keep the long string of text within a printable region of the page, but I don't want to merge the cell and wrap text. I'm familiar with left, mid, and right functions which will grab designated text from the cell above and place it on the next line, however I need to know how to design the function such that the command always grabs the text at a word break, and not in the middle of the word.

Alternatively, if there is a better way to approach the solution, I'm very interested.


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.

Rob.


Hi

In cell A1, I have a text string "PSI SWITCH 25 AMP SPNO ADJ 1-5 PSI 1/8" NPT PLA", each "word" separated by spaces. I would like to break this into to two(2) cells, where the first cell B1 can not be longer than 24 characters (including spaces), with the remainder being put in cell C1. My first thought was to use the text to columns function to break the string in to individual cells, then use the IF function with CONCATENATE to add the cells back together with spaces until there are 24 characters. IF there are more than 24 characters, then add less cells. Is this the correct thinking, Or is there a different way?

TIA

Rich


Very much hoping someone can help me w an Excel 2007 question.

I'm trying to create an IF function that returns text strings contained in other cells.
Normally I'd just place the text strings directly in the IF statement, however these strings contain quotation marks that must be there so they can't be placed directly.

For example:

L1: </span></strong></a><br />Price: <span style="text-decoration: line-through;">
L2: </span></strong></a><br />Price:
L3: =IF(O3<>""," text string in L1 "," text string in L2 ")

How do I make the result of my formula in L3 be a text string from another cell?


Hello all,

I have made a spreadsheet that does lots of functions on strings, however this one has stumped me. I need to take a string from another field and left pad it with zeros to 10 chars.
At the moment I have just done a bunch of nested ifs:

Code:

=IF(LEN(I7)>10,"INVALID",IF(LEN(I7)=3,CONCATENATE("0000000",I7),IF(LEN(I7)=4,CONCATENATE("000000",I7),IF(LEN(I7)=5,CONCATENATE("00000",I7),IF(LEN(I7)=6,CONCATENATE("0000",I7),IF(LEN(I7)=7,CONCATENATE("000",I7),IF(LEN(I7)=8,CONCATENATE("00",I7),IF(LEN(I7)=9,CONCATENATE("0",I7),I7))))))))


Which works, but is very messy.

Is there a neater way? Am I overlooking something simpler?

David


I am combining strings of text with linked numbers, and I can make it work two ways.

I have been doing it this way:
="text"&A1&"text"&B1

But I can also use the concatenate function:
=CONCATENATE("text",A1,"text",B1)

Is either way better? Is there a reason to use one way or the other, or is it just a personal preference?


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?

Thanks!


Hi!
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.
THE PROBLEM!
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!
Thanks,
Cj


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.


Hi
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


I'm trying to compare names in two lists to see if the same name appears, but the names are in two columns for first and last name. I would prefer them as ones string so I can use a MATCH command and return the location of the matched name in the list.

So is there a command that would allow me to paste the text of the first name after the text of the second name, or an equivalent of adding for text strings? so I could create a new column C (where first and last names are in columns A&B) with the formula in C2: =B2+A2 and fill down in the worksheet and get a column of merged text. If you add non-numerical cells you normally get an error, so it might be that the function is not actually adding, but that is metaphorically what I'm trying to do.

Thanks,

Brian




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!



Hi guys,

I'm trying to find a formula which in essence is the same as 'text to columns'.

I'll be pasting in post strings such as: A=B&C=D&E=F etc etc.

I need a formula to break the url so i have all the individual parameters listed i.e

A=B
&C=D
&E=F

Can this be done without a macro? Also i'm not fussed where the '&' belongs either at the front or the end of the text string.

Thanks in advance,
Lloyd

I don't know where to look to fix the following.
All cells in columns A thru E are 'format, cells, text'
The cells in col A contain either:
a) 1 character of text (a code in the application)
b) a 'long' text string (usually 10-35 characters or so)
a general macro processes them differently.
When a cell in Col A contains the long string I want to be able to see all
of it on the spreadsheet, and the rules are that
columns b,c,d,e will be null.

Dim Cola as string sets up a variable in the macro.

The problem is that all of the long text does not visually 'spread' across
columns b,c,d, etc.
I 'cleared contents' of cols b,c,d,e; no help.

when I 'detach' the macro call from the worksheet_selectionchange event, the
long text spreads just fine.

What do I do next?
Neal Z





Hello,

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 ";"

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

In Cell A2

Another fake line; why aren't I creative?

Desired
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...

Hallo,

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?

tg


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?!)

Thanks
Jeff





Hello,

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!


Hi!

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.
BR


I am trying to pull sections of a spreadsheet and seperate them into seperate tabs. Looking for a macro that can say look for a specified text string then copy all rows down to another text string. Example

Start Text String

Row 2
Row 3
Row 4
Row 5
Row 6
Row 7

Start Text String

I need to copy all the rows in the middle (or include the top and bottom strings doesnt really matter) and paste them to another sheet. Then go to the next instance where it says the string and copy those rows.

So below one sheet will have rows 2-7, another sheet will have rows 8-11. Is this possible? Thanks

Start Text String

Row 2
Row 3
Row 4
Row 5
Row 6
Row 7

Start Text String


Row 8
Row 9
Row 10
Row 11

Start Text String