How To Break Up A Text String?

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

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.

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.

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

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

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.

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

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

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!

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

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?


Thanks,
Daniel

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?


Thanks,
Daniel

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