Free Microsoft Excel 2013 Quick Reference

formula to extract partial content (text) of cell

Any suggestions on how to extract partial content from cells? For example,
if a cell's content has "MICROSOFT", I would like to extract "MICROS". Is
this possible? Many thanks in advance for your help.


Any suggestions on how to extract partial content from cells? For example,
if a cell's content has "MICROSOFT", I would like to extract "MICROS". Is
this possible? Many thanks in advance for your help.

So I need to extract partial contents of a cell.. right now in the cell it looks like

address: 9999

so I want my code to see the cell... dropp the "address: " and only retrieve 9999.

I know it's on google somewhere but I can't seem to describe it properly for the right results to come up. Help is very much appreciated. Thanks.

Text

I am looking for a formula to retrieve the contents of a formula in another workbook. For example, if cell A1 in workbook named A.xls contains a formula =2+2. Can anyone think of a formula to extract the whole formula from this workbook instead of the end result, ie 4?

Your input will be appreciated.

I have tried and tried... I'm looking for a formula to extract a phone
number out of an adjacent cell - eg, extract phone num from a cell containing
a want-ad description. Any ideas? thanks much!

I have a text field let's say in A2. in B2 I would like to have a formula that extracts a date form the text in A2.
Here's an example in A2: Audit report sent *10/12/08*.
By the way, it is assumed that the data entry person will always put the date between asterisks.
Is there a simple formula that can extract this?
Reason for the asterisks is because there is possible that there might be multiple dates in this text field. But for the date that I need to extract, it has been implemented that the data entry person will always put the asterisks before & after the date.
I just can't figure out the formula to extract.

Any suggestions/help?

I have a workbook with 12 sheets.

In every sheet, cell A1 contains: Name_of_Month 2008 (January 2008, etc.)

I wish to reference this via formula, but I only want the month name, eg. January or February -- dropping the 2008 part.

Is there a way to do that via a formula? To extract, from the beginning of the string to the first "space" character?

Using Excell 2000 ...
I have data being retrieved from another sheet
Cell A1 of the Input Sheet, for example might contain the words "This is
a TEST"
Cell A1 of the Output Sheet contains the formula: ='Input Sheet'!$A$1
.... thus displaying whatever the contents is of cell A1 of the Input Sheet.
I want to save the formula results in time ...
I want to convert the cell(s) of the Output Sheet to the results ...
getting rid of the equation(s).
I'm sure I have seen this before, I just cannot remember ??? ... getting
old!

Is there a function to extract the URL part of a hyperlink in another cell?

All I can see is the displayed text for the link.

if I use, for example, =A1 in another cell, all I get is a

copy of the displayed text.

What I am trying to do is automatically create a column of proper urls,
given a list

behind "displayed" urls.

Rufus

How to in one cell reference formula to:
Extract WORD created text numbers contained within parenthesis as numbers
for example:
103.70(4.25%)

After importing data from the web into an Excel column (for example cell H9)
such as:
I use a Peo Sjoblom suggestion:

=SUBSTITUTE(MID(H9,FIND("(",H9)+1,255),")","")

To extract and return the number within the parenthesis which is a “number
formatted as text”
4.25%

Question 1:
Is there someway to write the above formula so that it will return the text
formatted number as a number? (I gather that any formula would need to be
able to remove or replace the non-breaking character in the process.

Presently I use Bob Umlas’s Edit/Replace - Find – Alt numeric keypad 0160 –
Replace suggestion to convert after the fact – the only thing that seems to
work for me in converting text in Excel created from numbers in WORD to
numbers
i.e. Pete and others have pointed out that this removes the non-breaking
space character 0160.

How do I / can I incorporate this within the original formula?

Question 2:
Sometimes I really do want “text”
I also use the same above Substitution formula to extract desired “text”
from within parenthesis which works great!
Example: My String (XYZ) where XYZ is of variable length.
To return XYZ or DESIRED TEXT contained with the parenthesis.
The (XYZ) parenthesis is always at the right terminal end of the string.
BUT: It can be confused by either:
My String (something else (XYZ) as well as
My String (something else) and (XYZ)
My String something else) (XYZ)
Is there a way of writing the above SUBSTITUTION formula to eliminate the
occasional confusion?

Thanks again all,
M

Hi,
Need a formula to extract text from first column of data (column c in attached sheet) and result should look like below (or column E in sheet attached):
9˝o15 9.5
9˝u15 9.5
10u20 10
11u2 10
9o15 9
11˝u15 11
11o15 11

Much apprecated!

Is there a function to extract the URL part of a hyperlink in another cell?

All I can see is the displayed text for the link.

if I use, for example, =A1 in another cell, all I get is a

copy of the displayed text.

What I am trying to do is automatically create a column of proper urls,
given a list

behind "displayed" urls.

Rufus

Using Excell 2000 ...
I have data being retrieved from another sheet
Cell A1 of the Input Sheet, for example might contain the words "This is
a TEST"
Cell A1 of the Output Sheet contains the formula: ='Input Sheet'!$A$1
.... thus displaying whatever the contents is of cell A1 of the Input Sheet.
I want to save the formula results in time ...
I want to convert the cell(s) of the Output Sheet to the results ...
getting rid of the equation(s).
I'm sure I have seen this before, I just cannot remember ??? ... getting
old!

Good Morning,

I am trying to extract out any instances of dates in the below examples. There are usually characters in between the dates and the dates can be in different places as well. The dates are also in different formats but most are six digits. XXXXXX. Sometimes five. I was thinking to using LEN to separate the five digit and six digit cells. I would like to be able to pull out these dates for comparison. I would prefer a formula but if necessary, I can go the macro route. Sometimes there are other numbers in the cells, which make the pulling of the dates more difficult. The common date theme is the number "11" since these are all 2011 dates.
% AB CDEFGHK 11711 % ABCD AB ABCDEFG ABCDE AB FHK 11291 %,$12.34 110311DC/ABC %082911 277 ABCDEFGH %ABC DEF GH74 HIJ ABCDEFGH 112311

Thank you,
Adam

I'm after a formula to search for a string of text, and return the cell reference (most importantly the column letter) that this text is found in.

I've found a few formulas that return the row number, but nothing that returns the column letter.

Can anyone help me here?

Hello All,

I need some help devising a formula to extract text from a string.

If we take the following examples:

Example 1:

Hello, my name is Matty__from England

Example 2:

__Hello, my name is Matty__from England

Note that in example 1, there is a double space after Matty, whilst in example 2, there is a double space at the start and after Matty.

In both examples, I just want to extract "Hello, my name is Matty", which means "from England" is ignored in both examples, and, in example 2, the surplus spaces at the side are trimmed off.

Hope this makes sense, and any ideas most welcome!

Thanks,

Matty

I need to search for then extract a specific portion of cell data. Below, I
have provided an example of what I'm trying to do.

Column "A" (raw data) Column "B" (end result)
700001103 (x4) 4
8675-US (59) 59
8675-EU x 5 5
6330-02(18) 18
6330-02CE (x10 ) 10
6305-02 (1) 1
58516 (x117) 117

I hope someone can help me.

Thanks.
//Ken

Anyone, please help. I'd like to use a formula to count character in
range of cell which text are RED color.

--
Tii99
------------------------------------------------------------------------
Tii99's Profile: http://www.excelforum.com/member.php...o&userid=33142
View this thread: http://www.excelforum.com/showthread...hreadid=529506

Good Morning,

I am trying to extract out any instances of dates in the below examples. There are usually characters in between the dates and the dates can be in different places as well. The dates are also in different formats but most are six digits. XXXXXX. Sometimes five. I was thinking to using LEN to separate the five digit and six digit cells. I would like to be able to pull out these dates for comparison. I would prefer a formula but if necessary, I can go the macro route. Sometimes there are other numbers in the cells, which make the pulling of the dates more difficult. The common date theme is the number "11" since these are all 2011 dates.

% AB CDEFGHK 11711

% ABCD AB ABCDEFG ABCDE AB FHK 11291
%,$12.34 110311DC/ABC
%082911 277 ABCDEFGH
%ABC DEF GH74 HIJ ABCDEFGH 112311

Thank you,
Adam

This question is also posted at this site:
http://answers.microsoft.com/en-us/o...1-16bde322b9df

Anyone, please help. I'd like to use a formula to count character in range of cell which text are RED color.

Hello,

First of all, I want to thank all the regulars that have been such a tremendous and consistent help on here. You all are responsible for my education in excel which has proven to be invaluable. I am very grateful for everyone's help on here, not just those that answer and solve issues, but also those that ask questions and include sample workbooks.

I have received some raw data from another firm and they (for reasons unimaginable) had multiple values in a single cell. These values are critical and unique to each line. My goal is to extract these multiple values from their respective cells, into separate cells for each value.

For example, one cell (in column titled "Book and Page") will include: "1099 285 Harris County, CA"

All lines in the array include this issue for this column. I would like to formulate these values into 4 multiple cells. So the four ensuing cells would be: "1099"; "285"; "Harris County"; "CA" .

Same problem also exists on the legal description - one cell (in column titled "Legal Description") includes: "Section 19-16N-29E" . I would like to break this out into three adjacent cells, showing: "19"; "16N"; "29E" .

These are not uniform in that some legal descriptions include multiple sections. For example, one might be: "Sections 9 & 10-20N-13E" . In this scenario I am only interested in the last section listed, so the adjacent cells would read: "10"; "20N"; "13E" .

I have attached a sample workbook showing the original, raw data in the first worksheet. The second worksheet shows the added columns that I would like formulated to extract the values from the original cells.

Any advice or suggestions would be greatly appreciated.

Also, if it is significantly more complicated to account for legal descriptions with multiple sections, that can be left out as there aren't many of those and I could add those in manually.

Thank you in advance for taking the time to look at this.

moneySample_Extract values from single cell.xlsx

I want the text of cell n1 to be b1 + a string, or empty if b1 is
empty.
I currently have

=IF(B1<>"",B1,"")

which gives me the text if not empty, but how do I add my string of
text?

I am getting quite frustrated with this, so any help is very much appreciated

I have included an excel with my questions

1) How do I search the Text of Cell in a range and return the whole cell where content is found
2) How do I search a string of text in a range and return the whole cell where content is found

Many Thanks!

I have the following text in a cell eg :32A:110616GBP1,00
I need a formula to get anything after GBP, unfortunately the next cell could be
:32A:110616GBP1251,00

Any help much appreciated.

Hello Everyone,

Does anyone knows a formula to extract last three words of my data as displayed below:

Tahvili Sq., 17th St., No. 403,, Shiraz, Fars, IR
No. 31, Jafar Gholi Alley,West Nostrat,Tohid Sq.,Tehran,Tehran, IR
22 No - 2 West St - Seol St tehran, IR1995934733, IR
Qazi Asadollah Sq., No. 6, 1st Floor,, Kashan, Isfahan, IR
No 63, Abdoos Alley, Vaziri St,Tehran,Tehran, IR
No. 48, Shahid Mohsen Kashfi St., Beginning Of Shahid Madani Ave., Resalat Sq.Tehran, IR1634957514, IR
Faramarz Abbasi Sq., Baharan St., 7th Fajr Alley, No. 139,, Mashhad, Khorasan Razavi, IR
Javanh Blindalley,The Fifth,St Shikh Safi, Esfahan, Esfahan, IR
No. 11, East 15, South Shaghayegh, Ferdows Blvd., Sadeghieh Sq.,Tehran, IR1484983664, IR
Arghavan St - No 4, Esfahan, Esfahan, IR
Unit 2 .No 58 .Milad Cr.Azadshahr, Mashhad, Khorasan Razavi, IR
Mehrshahr, Eram Blvd., Milade 4 St., No.58,, Karaj,Tehran, IR
#48,2nd Fl.,Masoud Bazar,Zand Blvd., Shiraz, Fars, IR
Molla Sadra Ave. At Sheikh Bahai, Setade Farmandehi Sazemane Atashneshani Tehran, IR14358-63151, IR
Nabshe Meidane Shahid Mosavi Ghochani, Mashhad, Khorasan Razavai, IR
4th Floor, Khayyam Building, Moallem St.,, Rasht, Guilan, IR
No. 20, Sadoughi Blvd..Yazd, IR89197, IR
Khiaban Talaghani , Nabshe Khiaban Sepahbod Gharani Tehran, IR1581795411, IR
No. 51, West 188 St.,Tehranpars,,Tehran,Tehran, IR
No.7, Mirzaee Alley, Mojahedin St.,Tehran, IR11577, IR
Tehranmunicipality(1888) No.59 , Ansari Alley , Behesht St , Parkeshahr ,,Tehran,Tehran, IR
No. 27, 32th Gharani, Gharani Blvd.,, Mashhad, Khorasan Razavi, IR
No 25, Secend Laleh Alley, Mojahed Kabir Street, Laleh Sharghi Street, Jannat Abad,Tehran,Tehran, IR

I want them to be extracted like this:

Shiraz, Fars, IR
Tehran,Tehran, IR
tehran, IR1995934733, IR
Kashan, Isfahan, IR
Tehran,Tehran, IR
Tehran, IR1634957514, IR
Mashhad, Khorasan Razavi, IR
Esfahan, Esfahan, IR
Tehran, IR1484983664, IR
Esfahan, Esfahan, IR
Mashhad, Khorasan Razavi, IR
Karaj,Tehran, IR
Shiraz, Fars, IR
Tehran, IR14358-63151, IR
Mashhad, Khorasan Razavai, IR
Rasht, Guilan, IR
Yazd, IR89197, IR
Tehran, IR1581795411, IR
Tehran,Tehran, IR
Tehran, IR11577, IR
Tehran,Tehran, IR
Mashhad, Khorasan Razavi, IR
Tehran,Tehran, IR

As you see the delimiter is not always the same prior to third word to the last; it might be sometimes a comma, dot or even a space.

And sometimes the second word itself consist of two or more words with spaces in between them. The red color represent those cells.

And sometimes the second word itself consist of alphanumeric characters with a hyphen between them. The blue color represent those cells.

Your help is highly appreciated, guys.

Thanks,
Vaalf