| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text removal question
I have a column (A1) that has text similar to the following:
Adult Services-DMHAS, Bridgeport A100215249400 I want to take only the text to the right of the last space. The resulting column (A2) would have the following text in it: A100215249400 Can someone tell me the formula for this?-- Thanks You all are teaching me so much |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Text removal question
=RIGHT(A1,MATCH(" ",MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1))),1),0))
This is an array formula that must be entered with CNTRL-SHFT-ENTER rather than just the ENTER key. -- Gary''s Student - gsnu200819 "knowshowrosegrows" wrote: I have a column (A1) that has text similar to the following: Adult Services-DMHAS, Bridgeport A100215249400 I want to take only the text to the right of the last space. The resulting column (A2) would have the following text in it: A100215249400 Can someone tell me the formula for this?-- Thanks You all are teaching me so much |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Text removal question
Shazam!
That was a quick reply. Thanks There are about four concepts in that formula that I will need to read up on to understand. Great - Unfortunately, when I pasted it in and hit CNTRL-SHFT-ENTER I got #N/A. Any ideas? -- Thanks You all are teaching me so much "Gary''s Student" wrote: =RIGHT(A1,MATCH(" ",MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1))),1),0)) This is an array formula that must be entered with CNTRL-SHFT-ENTER rather than just the ENTER key. -- Gary''s Student - gsnu200819 "knowshowrosegrows" wrote: I have a column (A1) that has text similar to the following: Adult Services-DMHAS, Bridgeport A100215249400 I want to take only the text to the right of the last space. The resulting column (A2) would have the following text in it: A100215249400 Can someone tell me the formula for this?-- Thanks You all are teaching me so much |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Text removal question
I got it! Your formula worked. Thanks
-- Thanks You all are teaching me so much "Gary''s Student" wrote: =RIGHT(A1,MATCH(" ",MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1))),1),0)) This is an array formula that must be entered with CNTRL-SHFT-ENTER rather than just the ENTER key. -- Gary''s Student - gsnu200819 "knowshowrosegrows" wrote: I have a column (A1) that has text similar to the following: Adult Services-DMHAS, Bridgeport A100215249400 I want to take only the text to the right of the last space. The resulting column (A2) would have the following text in it: A100215249400 Can someone tell me the formula for this?-- Thanks You all are teaching me so much |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Text removal question
A1 and A2 are not columns, so I'm not totally clear where your data is.
Perhaps this normally entered formula does what you want... =TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),99)) -- Rick (MVP - Excel) "knowshowrosegrows" wrote in message ... I have a column (A1) that has text similar to the following: Adult Services-DMHAS, Bridgeport A100215249400 I want to take only the text to the right of the last space. The resulting column (A2) would have the following text in it: A100215249400 Can someone tell me the formula for this?-- Thanks You all are teaching me so much |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Text removal question
While I think the formula I just posted may be a little more efficient than
this one, I'm thinking your problem with the formula Gary''s Student posted might be in where you pasted it. If you pasted it directly into the cell, Ctrl+Shift+Enter won't work... you have to paste the formula directly into the Formula Bar and then hit Ctrl+Shift+Enter from that location. -- Rick (MVP - Excel) "knowshowrosegrows" wrote in message ... Shazam! That was a quick reply. Thanks There are about four concepts in that formula that I will need to read up on to understand. Great - Unfortunately, when I pasted it in and hit CNTRL-SHFT-ENTER I got #N/A. Any ideas? -- Thanks You all are teaching me so much "Gary''s Student" wrote: =RIGHT(A1,MATCH(" ",MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1))),1),0)) This is an array formula that must be entered with CNTRL-SHFT-ENTER rather than just the ENTER key. -- Gary''s Student - gsnu200819 "knowshowrosegrows" wrote: I have a column (A1) that has text similar to the following: Adult Services-DMHAS, Bridgeport A100215249400 I want to take only the text to the right of the last space. The resulting column (A2) would have the following text in it: A100215249400 Can someone tell me the formula for this?-- Thanks You all are teaching me so much |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Removal of text from a cell containing both text and numbers | Excel Discussion (Misc queries) | |||
| paranthesis removal | Excel Worksheet Functions | |||
| Macro removal | Excel Discussion (Misc queries) | |||
| Number removal | Excel Worksheet Functions | |||
| Year row removal | Excel Worksheet Functions | |||
Linear Mode
