Free Microsoft Excel 2013 Quick Reference

Match part of a text string with another

Hi,

I am trying to match part of a text string with another entry. I know if they were exact strings I could do this;

=IF (A1 = E1, 1, 0)

The text in A1 may be "randomtext" the trxt in E1 may be http://randomtext or it could be http://www.randomtext

I want something that basically just says if E1 contains A1 then YES.

Any ideas anyone??

Thanks Minx!

:D


Post your answer or comment

comments powered by Disqus
Hi All,

Had a good search around and can't find what I need, so any help appreciated...

I need a macro that will rename the active worksheet to part of a text string in a cell, then use the same name to save the entire workbook with the current date added (in UK format).

The cell will always be C2.
The name needs to ignore the first 9 characters in C2, but use everything after that which will be of variable length (40 characters would be enough though - if this helps).

The file needs to save to this directory: G:Trainingwaitlists

Thanks.

Hi

I am dealing with UK post code (US Zip) data which has been supplied to me in full. However I only need part of each string. I need to copy and paste the strings into an adjoining cell ommitting the last two characters. Unfortunately the text strings change length (some are six characters some are seven). There must be a formula that will let me select just part of a text string other than the standard Left/Right excel formulas.

ie

Original: B80 7PA
Required : B80 7

However it could also be

Original: BS5 67QE
Required: BS5 67

Any help is much appreciated.

I need to delete just part of a text string that will appear in many
different cells.

For example. 1234-DAW001 5678-DAW001 8254-DAW001 etc etc

Is it possible to delete the DAW001 reference in each cell but leave the
number alone, without going into each individual cell. Would this require a
Macro?

I have 1900 cells that have text strings like this and I would like to
change them all to the first 4 digits only

Hello.

I wish to make Excel convert only part of a text/value in a cell. The syntax for the text will be like this:

0025-DO-20B828-CC3-0

The first for digits are the size in metric mm's, is this part is 25mm in size.
The text is the name of a piping spool. The sizes range from 0020mm to 0500mm. I have to convert about 200 spools, so a spreadsheet would make life a lot easier.

I want to display it as this:

1"-DO-20B828-CC3-0

Can Excel do this? I am using Excel 2003.

Is it possible to format part of a concatenated string?

For instance I have the following formula

=CONCATENATE(A5,G5)

This returns the following

Sales have decreased by -7260.39

The -7260.39 is the value of cell G5.

I want to be able to format this as Currency with no decimal points. However when included in the Concatenate string it returns the value as text. Is there anyway I can format it within the Concatenate string?

Is it possible to remove the letter part of a text string and leave just the number?
My String can have 2, 3 or 4 letters followed by 1, 2, 3 or 4 numbers.

For example ABK1234, I would like to be left with 1234

I have a range of cells that have numbers and a comma at the end.

235123,
123563,
723633,
123578,

I'd like a macro that would create a single text string of all the cells. The above would convert to:

235123,123563,723633,123578,

Is there a way to do that with a Macro? The number of cells with data in them changes. I know how to write the loop, it's just adding the value of one cell to the end of a text string in another that I cannot figure out.

I want to copy the time from within a text string. The problem is that the time will be placed at a different position depending on the length of the preceding text. In the first example below, the time is placed 16 characters from the left and 17 from the left in the second.

examples:

Sthl 7th Jan - 22:55 2m Hcap Hrd

Sthl 27th Jan - 22:55 2m Hcap Hrd

The only constant is "-" before the time.

Anyone have any ideas for I can extract the time?

Many thanks

Hello. I need to extract a $ value out of a text string in A3 and move it to F2, so on and so forth, for the entire worksheet. I have included sample data in the attached file.

Thank you!

Hi Folks,
I'm an absolute gumby at working out expressions for finding part of a text string and was hoping someone could help out. I need to extract the date from the following string:

15205_26-08-10_A_LR.pdf

So far I have:
which will get me "15205" However I can't seem to get the date after the first underscore.

Any help would be appreciated.

Hi!

I am trying to pull out some text from a list of adresses - where i want to get the postal code and city name out of each adresse - but the postal code and city names are different for each line, which makes it difficult to do

As you can see i have put a ring around the part of the text string that i need to pull out - but i want to do it quick and easy for each line - but the text strings are different lenghts -

Anyone got any ideas?

Manually is not an option since there are 60,000 somthing lines

I need to separate the number from the right of a text string into its
own cell.

For example in the data:

"Salaries - Aides / Assistants 3708"
"Classroom Stationery & Materials 21"

I want to return 3708 and 21 as the value of the formula. Note that
there are spaces and the number lengths vary.

Does anybody know a formula I can write to extract this floating
numeric string which is not a fixed length and at the right of the text

string.

What this means is that I need to find the position from where the
numbers in the string start, and using the MID and LEN functions I can
specify this value to get the number extracted into its own cell.

Thanks.

I need to separate the number from the right of a text string into its
own cell.

For example in the data:

"Salaries - Aides / Assistants 3708"
"Classroom Stationery & Materials 21"

I want to return 3708 and 21 as the value of the formula. Note that
there are spaces and the number lengths vary.

Does anybody know a formula I can write to extract this floating
numeric string which is not a fixed length and at the right of the text

string.

What this means is that I need to find the position from where the
numbers in the string start, and using the MID and LEN functions I can
specify this value to get the number extracted into its own cell.

Thanks.

Hallo folks,

Am trying to figure out if a user-defined function can be created to
alphabetically re-order a text string with multiple words.

For example.
"Hotel Paris Hilton" should convert to "Hilton Hotel Paris"
"Paris" is then the last word because "H" is before "P" in the alphabet
"Hilton" is the first word because "i" is before "o" in the Alphabet and so
on and so forth.

For any help I would be really grateful.
Thanks
Mike

Hi
Iam trying to find a formula that will search the beginning part of a cell, for a certain piece of text, and then counting the result if criteria in another column is true.

I have used the sumproduct formula before, but this only works if the cells specifically contain the text that is searched for.

=SUMPRODUCT(($B$2:$B$72="N/R")*($C$2:$C$72=1))

the data that I have has the "N/R" at the beginning of each cell eg Cell B8 = N/R BLACK DRAWING PAD or some of the text looks like this eg Cell B12 = (N/R)POSTER PAINT NEON

so what I want the formula to do, is search column B for any cells that have N/R or (N/R) at the beginning. then count if it matches certain data in column C

any help will be much appreciated

Hi,

I'm trying to create an if statement, with no luck.

Col A Col B Col F
Apples value nanas
Bannanas wackos
Coconuts value

Formulas in Col B:
If any of the text in Col A matches another cell with "nanas" or "wackos" in
it (in this case F1 and F2), then leave the cell blank, otherwise X. X being
another formula that returns some random value.

Essentially I'm trying to create a reference list in column F, for as many
references as possible, maybe up to 20, so if any of the values in col A
match any of the text strings in column F, it leaves the cell blank.

I tried, couldn't do it. Saw something with =Countif(A1,"*nanas*") but
couldn't adjust it.

Thanks very much.

How can I display the contents of a cell in another as part of a text
string.

Such as... "The answer is "B4""

where B4 is the contents of cell B4?

Thanks

Hi,

I'm trying to create an if statement, with no luck.

Col A Col B Col F
Apples value nanas
Bannanas wackos
Coconuts value

Formulas in Col B:
If any of the text in Col A matches another cell with "nanas" or "wackos" in
it (in this case F1 and F2), then leave the cell blank, otherwise X. X being
another formula that returns some random value.

Essentially I'm trying to create a reference list in column F, for as many
references as possible, maybe up to 20, so if any of the values in col A
match any of the text strings in column F, it leaves the cell blank.

I tried, couldn't do it. Saw something with =Countif(A1,"*nanas*") but
couldn't adjust it.

Thanks very much.

How can I display the contents of a cell in another as part of a text
string.

Such as... "The answer is "B4""

where B4 is the contents of cell B4?

Thanks

I am trying to see if part of a text string matches part of another text string.

Example:
In C1:C100, I have names.

In C101:C300, I have names that are similar to those in C1:C100, but may include a "The" in front or have "Inc" in the back, or both.

Is there a way for me to check if each of the names in C101:C300 match one of those in C1:C100 and then get the matching value from C1:C100?

Thanks for any help or direction you can give.

Hi there, 1st time poster - I'm struggling with a VLookup and would really appreciate some expert advice.

Trying to lookup part of a text string where the names are very similar and return an associated value, like this;

Col A
A A Jones
A C Davis
H&J Cump
Travis

Col B Col C
A A Jones Ltd 1000
A C Davis & Co 2500
H&J Cumpton 300
Travis Ltd 750

I want to bring back the value in Col C against the name in Col A - Hope that makes sense!

Thanks for looking

Stock Guy

Hi,

I want to omit the right part of a text string that starts with "(" and ends
in ")" while returning the left part of the text string. I would have a cell
that contains a name such as "Lysell, Kent". Right after this name a number
in parenthesis is concatenated. For example, I would need to return only
"Lysell, Kent" from "Lysell, Kent(111)". This number might be 1 to 4 digits
long. Is there a formula that would omit this number in parethesis -
including the parenthesis - while returning just the name?

Thanks in advance!
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.948-9557

Trying to match the first six characters of a text string to text characters
in a data table. I'm trying to use the VLOOKUP Function, but I keep getting
the contents of the second to last cell in my designated column. I'm having
a problem both with truncating the text string, and also using text to search
for things. Anyway to convert similar text characters to unique numbers?
Looking for any suggestions

Hello.
I have a large spreadsheet that lists many text strings in a single column.
The text strings include the name of a company, product, platform, and
language. I would like to isolate the language from the text string and
populate the cells in another column with only the name of that language. The
text string does not follow a consistent format where there are the same
number of words or where the language is always in the same position in the
sequence. There are 24 different languages that are used in the text strings,
and some of the languages are more than one word, such as "Chinese
Traditional" or "Chinese Simplified."

Using the formula =IF(SEARCH("German",D44),"German") works if the word
German is part of the text string in cell D44. However, I need to be able to
add all of the other languages into a single formula to verify line by line
what language is in the text string.

When I try to add other languages to the formula, such as
=IF(SEARCH("German",D44),"German"),IF(SEARCH("Dani sh",D44),"Danish"), the
result is #VALUE!, which is not what I want.

Can this be accomplished through a formula, and if so, how? Would it work
better to set this up as a macro? If so, any guidance on how to do that would
be much appreciated.

Thank you!


No luck finding an answer? You could always try Google.