split the content of cell

how do we split the content of one cell into two cells
example :

6'X3'     =      6  and 3

0
Utf
5/28/2010 6:58:01 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

2 Replies
823 Views

Similar Articles

[PageSpeed] 26

Left part:
=LOOKUP(6.022*10^23,--LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))))
Right part:
=LOOKUP(6.022*10^23,--RIGHT(SUBSTITUTE(A1,"'",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1,"'",""))))))


-- 
Regards!
Stefi



„cosmic” ezt írta:

> how do we split the content of one cell into two cells
> example :
> 
> 6'X3'     =      6  and 3
> 
0
Utf
5/28/2010 8:26:01 AM
Try

=--SUBSTITUTE(LEFT(A1,SEARCH("X",A1)-1),"'",)

=--SUBSTITUTE(MID(A1,SEARCH("X",A1)+1,10),"'",)


-- 
Jacob (MVP - Excel)


"cosmic" wrote:

> how do we split the content of one cell into two cells
> example :
> 
> 6'X3'     =      6  and 3
> 
0
Utf
5/28/2010 9:16:01 AM
Reply:

Similar Artilces:

is it possible to change print ink color based on cell value?
Is it possible to change print ink color based on value in a cell for a spreadsheet application? Example: If a cell's value is over 250, can I have it print the number in red ink to draw my attention to it?? You can use Conditional Formatting (Format>Conditional Formatting) to test the value and set the font colour accordingly. -- HTH RP (remove nothere from the email address if mailing direct) "Dave The Mechanic" <Dave The Mechanic@discussions.microsoft.com> wrote in message news:0B63AB99-5824-4AFB-A206-D4CDF1BB9D48@microsoft.com... > Is it possible to chang...

Using a formula in a Hyperlink Cell reference
I have a workbook with approx 60 worksheets in it. I have set up hyperlinks on the 1st sheet to take me directly to the worksheet I want to see. Is there any way of using a function in the Hyperlink cell reference, so the link will automatically position me at the right cell on the target sheet? I thought I could use the COUNTA function to determine the first non-blank cell, but the hyperlink doesn't seem to like it. Hi what HYPERLINK formula are you currently using? -- Regards Frank Kabel Frankfurt, Germany "sslabbe" <sslabbe@discussions.microsoft.com> schrieb i...

Can Excel find a cell based on two criteria?
My worksheet does not have column headings, but I do have unique data in a particular row. The data is a Date. The rows contain unique entries also (2 characters, always unique example JO,WO,XX,RR,TV ETC..) Is there a way, to have excel find the cell that would be in the column that contains a date (entered by user)and the Row that contains the initials (again entered by the user)? If so, can a userform be created for the user to enter the information (date mm/dd/yy) + (XX), along with the New data that goes into the 'intersecting' cell? c d e ...

Summing up specific cell references and its correlating value
I want to get a total sum of "A" without having to add up eac individual "A" value. Example: January A, "25" (b1) B, 24 D, 15 February A, "30" (b8) C, 20 B, 5 April B 50 C 35 My formula for A's total sum now reads: =$b$1+$b$8 The "$" signs are there because periodically the data in that specifi month is sorted by descending amounts. Is there a "sumif" formula that can find all the "A's" and will sum u all values in the adjacent cell? Thanks in advance. - -- Message posted from http://www.ExcelForum.com H...

IE message: other content blocked
Hi, Sometimes I get a message from IE 8: "Pop-up is blocked. Also, to help protect your security, Internet Explorer blocked other content from this site." Does anyone know how to find out what this "other content" is? Failing that, does anyone know, in general terms, what general type of "content" it might be? What sort of website behaviour would trigger the message? Thanks! Please try the IE7/IE8 group. The recommended newsgroup is microsoft.public.internetexplorer.general In a proper newsreader: news://msnews.microsoft.com/microsoft.publ...

Cannot draw the relationship after splitting the database
I have used the relationship ribbon many times and it work just fine. Recently I added another table to my database, establish the relationship with two other tables and everything looks good. The odd part is after I split the database, either I open the back end or the front end Access always giving me error so I cannot see the pictorial relationship. The "Visual Studio Just-In-Time Debugger" window pop-up with a message "An unhandled Win32 exception occured in MSACCESS.EXE [6072]" Is there a bug in Access that I don't know about, is there a fix to ...

When copying from "date" cell to "general" cell, how to keep date.
An Excel question for you: when I try to copy data from a "date" formatted cell over to a concatenate formula in a "general" formatted field, it transfers the data over as the boolean value. This is causing endless headaches because I am trying to upload the information into our financial software. Do you know a solution for this? I think you mean the date comes over as the serial value. If not, this may not be right. In the concatenate function, embed the Text function. ex: A1 has 5/16/2004, B1 has "TEST" in it. C1 formula would be =concatenate (TEX...

Excel Newb: Splitting First name and Last name
I have a long list of names in column A that look like this: Ana Trujillo Antonio Moreno Thomas Hardy Christina Berglund How can I split out the names by first name and last name with first name going in B and Last name going in C? Thank you very much. You can use the Text To Columns tool on the Data menu, or you can use formulas. If the full name is in A1, you can get the first name with =LEFT(A1,FIND(" ",A1)) and the last name with =MID(A1,FIND(" ",A1)+1,99) However, you need to decide what to do with names with more than two components. E.g., "John David Smit...

Cell and tab color
Cell and tab color do not appear while working with the sheet bu does preview and print How can I get the color to appear while working with the sheet The high contrast setting may be turned on. There's information in the following MSKB article: OFF: Changes to Fill Color and Fill Pattern Are Not Displayed http://support.microsoft.com/?id=320531 RonK wrote: > Cell and tab color do not appear while working with the > sheet bu does preview and print How can I get the color > to appear while working with the sheet -- Debra Dalgleish Excel FAQ, Tips & Book L...

Go Immediately To A Particular Cell After Inputting Data In Another Cell And Hitting Enter
I'm inputting data in E3 and after I hit enter I want B2 to be selected. But only for E3 and only in one worksheet. And possibly a further step... Sometimes the formula in B2 has picked up data from another place depending on what value was entered in E3. If it's done this then I won't need to go to B2, I'd want to go to A8 after E3 instead of B2. Put this in the code module of the worksheet that contains the ranges you are working with. Right click the sheet name tab, select View Code from the pop up menu and paste this into the code window that appears on scre...

separating text and numbers contained in one cell
We received a spreadsheet from a customer, containing data which w would like to improt in to a database. The problem is the numerica values and text descriptive fields are contained in one cell. Is ther a way to grab all text in a spreadsheet and move it over one colum leaving it in the same row? What I would like is for the numbers t stay in column one and the descriptive text to be cut/moved in to th adjacent cell in column two. Any help is appreciated. Thanks, DW -- Message posted from http://www.ExcelForum.com Assuming the numbers and text are consistant, like addresses (1234 Alame...

In Excel
I want to effect a mass change to the sign of numeric values in a range of cells (rows and columns). put -1 in an empty cell, copy it, select the range of cells to be modified, select Paste Special and pick Multiply. MaryKaye wrote: > I want to effect a mass change to the sign of numeric values in a range of > cells (rows and columns). Put -1 in an un-used cell and copy it. Then select the cells you want to update and: Edit > PasteSpecial > multiply -- Gary''s Student - gsnu201001 "MaryKaye" wrote: > I want to effect a mass ch...

show sum of selected cells
what function do i need to enable to allow the sum of selected cells to show on the bottom right of the worksheet View, Status Bar derwood Wrote: > what function do i need to enable to allow the sum of selected cells to > show on the bottom right of the worksheet -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21059 View this thread: http://www.excelforum.com/showthread.php?threadid=487537 After status bar is visible, right-click on it; choose desi...

find and replace with zero as the first digit in a cell
I have a spreadsheet with part number, many of which begin with 0 and Excel automatically removed the 0. I want to use find and replace for blocks of this to convert it back but while it indicates it found and replaced X number of entries, there is no chage to the data. I've got the cells formatted as text and if I do the replacement one at a time by typing it seems to hold. Thanks George, Apparently your part numbers are numbers, not text. Excel stores the value of a number, not not its digits, and leading zeroes don't play a part. You can format it for leading zeroes (Forma...

cannot view all of text in large cell, even though I have it to w.
I have cell format to wrap text and it works fine to a p[oint then no more text is displayed....casn increase the size of the cell, but still only so much will display....rest of the cell show blank. Hi +the limit is 1024 characters. You can extend this with manually inserting linebrekas using aLT+ENTER -- Regards Frank Kabel Frankfurt, Germany sydme wrote: > I have cell format to wrap text and it works fine to a p[oint then no > more text is displayed....casn increase the size of the cell, but > still only so much will display....rest of the cell show blank. ...

SUMIF based on cell shading
Is it possible to write a SUMIF formula based on cell shading? If so, how? I want to add up cells based on the shading of the cell. Thanks. How did the cells get shaded? If by Conditional Formatting then use the same criteria in your SUMIF If manually shaded you will need VBA See Chip Pearson's site for code. http://www.cpearson.com/excel/colors.aspx Gord Dibben MS Excel MVP On Wed, 17 Feb 2010 12:55:01 -0800, IlliniEric <IlliniEric@discussions.microsoft.com> wrote: >Is it possible to write a SUMIF formula based on cell shading? If so, how? I >...

Multiple hyperlinks in one cell
Is it possible to have multiple hyperlinks in one cell (referencing say 5 different cells)? Try this: Assuming they are all in the same worksheet, select those cells and give them a range name: In the Name box (just above the Col_A heading) type: rngMultiCells, then press [Enter] Then, select your hyperlink cell. Insert>Hyperlink Click: Place in this document Select the range named rngMultiCells Click [OK] Now, when you click the hyperlink....those cells should all be selected. Does that help? *********** Regards, Ron "Rednosebob" wrote: > Is it possible to have mul...

getting data from another cell
Hi I'm having a column A2 of full name eg (HOWES,CARRIE,L) I want to copy the first name, last name and the inital to three another cells like A3 LAST NAME = HOWES, A4 FIRST NAME = CARRIE, A5 MI = L Please help me to do it . Thanks jack Go to Data > Text to columns and use comma as your delimiter. HTH Jason Atlanta, GA >-----Original Message----- >Hi > I'm having a column A2 of full name eg (HOWES,CARRIE,L) I want to copy >the first name, last name and the inital to three another cells like A3 LAST >NAME = HOWES, A4 FIRST NAME = CARRIE, A5 MI = ...

Get rows from another tab where cell in column is empty
I have a spreadsheet where I track amounts owed to subcontractors and when they were paid. I'm trying to create another tab where I pull all rows that have not been paid. The only thing anyone has suggested so far is autofilter but that isn't what I'm looking for. Is there a function for this or would it have to be a macro? I don't really want to do a macro. I might just build this in Access, but I thought I'd try this angle first since it's already set up in Excel. I really appreciate any help that could be provided. gg If you don't like AutoFilter, then a ...

Thumbnail pictures in cells don't sort with rest of row
Hi- We use Excel for student grades here and we like to print the pictures of each student in their assigned small group. These small groups change membership frequently and the "Sort" function in excel is an easy way to keep track of which student is assigned to ehich small group tutor. I've placed a small thumbnail photograph of each student into a column "PHOTO". The thumbnails have been converted to "picture metafile" format and are entirely contained within the cell. Each picture property is set to "Move, but don't resize". Tools->Optio...

deleting the repeating content
hi, I have some name like this To Jimm Jimm And And How to delete to repeating name, turning them into To Jimm And Thanks. Try Advanced Filter, there is a good tutorial here http://contextures.com/xladvfilter01.html Regards, "ZR" <anonymous@discussions.microsoft.com> wrote in message news:1E05172F-00A7-4392-848B-D7CBA8636620@microsoft.com... > hi, I have some name like this: > > Tom > Jimmy > Jimmy > Andy > Andy > > How to delete to repeating name, turning them into: > > Tom > Jimmy > Andy > > Thanks. Hi see: http://www.cpear...

URGENT!!! Model a variation of the contents of SQL Tables.
We are interested in knowing if a functionality exists to draw a variation of the contents of SQL Tables. For instance if you have the following tables: Objects Object_ID Object_Name Object_Relationship Flow (eg. In/Out) Object_ID We would like to know if the following output may be produced: ObjectA _ ObjectB | | ObjectC We have a table that contains the flows between objects and we would like to see Visio draw the relationships/flows between the objects. It basically has to expand the Object-Relationship table as above into all instances/layers. ...

Combining Cells #2
I wish to combine two cells, want the cell to appear as Chicago Golf Club 04/15/04, where Chicago Golf Club is cell a1 and 04/14/04 is cell b1. When I use the formula a1&" "&b1 the date is shown as a number. Is there anyway it can be shown as a date? This cell will then be used to save the file, using a macro. Hi try =A1&" "&TEXT(B1,"MM/DD/YY") -- Regards Frank Kabel Frankfurt, Germany ABYPFCS wrote: > I wish to combine two cells, want the cell to appear as Chicago Golf > Club 04/15/04, where Chicago Golf Club is cell a1 and 04/14/04 ...

Linking Cells from Multiple Sheets
Hello I have a workbook with 95 sheets containing information for different customers and I would like to extract information from 3 cells in each sheet to a 96th sheet to use this for a mailmerge document. Basically I want the information in B1 of all the sheets to copy to A2-A96 of the new sheet, i.e. Sheet 1 B1 Baker Sheet 2 B1 Toms Sheet 3 B1 Peters and what I would like on sheet 96 is A2 Baker A3 Toms A4 Peters etc I have tried to use indirect formulae but I cannot get it to work. Please can anyone point me in the right direction. TIA You haven't told us what you we...

Entering Mutliple times in a cell #2
So with the 2 replies I have so far, I gather that I can't put the 3 times in one cell and Excel is able to calculate it?? -- gary66 ------------------------------------------------------------------------ gary66's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=16060 View this thread: http://www.excelforum.com/showthread.php?threadid=275186 Yes .. you'll get #VALUE! -- Rgds Max xl 97 --- Please respond in thread xdemechanik <at>yahoo<dot>com ---- "gary66" <gary66.1f89e0@excelforum-nospam.com> wrote in message news:gary66.1f...