How can I pull out position 4 in a string?

Column A contains a string

MyString
aaaaaa
bbbbbb
cccccc
dddddd
eeemee
fffmff
gggmgg

I want the value of B1 to be the value of A1 unless the 4th character in A1
is a "m".  So column B would be
B1=aaaaaa
B2=bbbbbb
B3=cccccc
B4=dddddd
B5 = empty cell
B6 = empty cell
B7 = empty cell

What's the simplest way to do this?




0
nowhere (47)
1/14/2004 6:50:39 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
390 Views

Similar Articles

[PageSpeed] 39

=IF(MID(A1,4,1)="m","",A1)

-- 

Regards,

Peo Sjoblom


"Stephanie" <nowhere@microsoft.com> wrote in message
news:OjT2P9s2DHA.2528@TK2MSFTNGP10.phx.gbl...
> Column A contains a string
>
> MyString
> aaaaaa
> bbbbbb
> cccccc
> dddddd
> eeemee
> fffmff
> gggmgg
>
> I want the value of B1 to be the value of A1 unless the 4th character in
A1
> is a "m".  So column B would be
> B1=aaaaaa
> B2=bbbbbb
> B3=cccccc
> B4=dddddd
> B5 = empty cell
> B6 = empty cell
> B7 = empty cell
>
> What's the simplest way to do this?
>
>
>
>


0
terre08 (1112)
1/14/2004 6:56:58 PM
=IF(MID(A1,4,1)="m","",A1)

-- 

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"Stephanie" <nowhere@microsoft.com> wrote in message
news:OjT2P9s2DHA.2528@TK2MSFTNGP10.phx.gbl...
> Column A contains a string
>
> MyString
> aaaaaa
> bbbbbb
> cccccc
> dddddd
> eeemee
> fffmff
> gggmgg
>
> I want the value of B1 to be the value of A1 unless the 4th character in
A1
> is a "m".  So column B would be
> B1=aaaaaa
> B2=bbbbbb
> B3=cccccc
> B4=dddddd
> B5 = empty cell
> B6 = empty cell
> B7 = empty cell
>
> What's the simplest way to do this?
>
>
>
>


0
nicolaus (2022)
1/14/2004 6:59:09 PM
Reply:

Similar Artilces:

Looking for an excel function which can mirror opposite the data
Dear sir, There are 2 data sets is assumed running in A1 down, viz.: From A1 down to A10, are showing a set of number: from 1 to 10 In the cell from B1 to B10, are showing from 10 to 1, which is decreasing from 10 to 1. My question: I want to know is there any excel formula which I can place in B1 and pick A10's number; B2 pick A9's number and etc. I know there is an easy way to do it, just type "=A10" in the cell of B1, but it is quite difficult to do it when I have more than 200 number, e.g. A1 to A200. Thanks for your advice, Wilchong -- Message posted via OfficeKB...

can't insert, delete or move pictures
can't insert, delete or move pictures Sheet protected? parker wrote: > > can't insert, delete or move pictures -- Dave Peterson ...

Can I replace word by word in italics?
Is there a search/replace feature in Publisher that allows you to replace a word by the same word in Italics? -- Marv Not in Publisher, but you can in Word using wildcards. Might try copy/paste into Word, do the editing, copy/paste back into Publisher Help here http://www.gmayor.com/replace_using_wildcards.htm -- Mary Sauer http://msauer.mvps.org/ "WISEMANOFNARNIA" <COHENMARVIN@LYCOS.COM> wrote in message news:1f5149a6-f337-4db7-9aed-78132e9c9ae8@j12g2000vbl.googlegroups.com... > Is there a search/replace feature in Publisher that allows you to > replace a word by ...

Pulling data for a report from a form
Hi, I have a product report list that want to show the on hand qty. so i have this formula in a report field: =onHand([pkProductID],Forms!FrmMyDate!InvDateQty) but when i preview the report it shows #name? instead. Thank you -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access/200912/1 To refer to a textbox on another (open) form, use: =Forms!FrmMyDate!InvDateQty -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "josros60 via AccessMonster.com" <u56159@uw...

Text String to Unicode
Hi, How do I convert a text string (from a text file) that has escaped unicode charactes (\u0635\u0641\u0627\u0631) to unicode chars? Thanks, Guilherme R. Rolim Look at macros A2W etc. -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "Guilherme R. Rolim" <grrolim@yahoo.com.br> wrote in message news:OXR7gzxkDHA.988@TK2MSFTNGP10.phx.gbl... | Hi, | | How do I convert a text string (from a text file) that has escaped unicode | charactes (\u0635\u0641\u0627\u0631) to unicode chars? | | Thanks, | | Guilherme R. Rolim | | | | I'll try to put some more details, mayb...

can not delete emails
i went out of town and returned with 3000+ emails. some i can delete, some i can not. i have tried to repair and fix, i have tried to create a new profile and import/export everything. any ideas? thanks in advance. yes, the cannot move item(s). The initial error i get when trying to delete is: "the message interface has returned an unknown error. if this persists, restart outlook". The only thing i can think of is a damaged profile??? >-----Original Message----- >Do you get an error when trying to move the item to another folder (e.g. >move the item to the deleted ...

How can I line up 2 columns with similar information?
I have 2 columns with names in each and I'd like to line them up next to each other. I think we'll need more information, Kathy. I don't understand what you're trying to do. Can you give exactly what's up? ************ Anne Troy www.OfficeArticles.com "Kathy" <Kathy@discussions.microsoft.com> wrote in message news:A5C0BC96-7E27-4C55-89A3-03513FC0D409@microsoft.com... >I have 2 columns with names in each and I'd like to line them up next to >each > other. Column A has employee number, Column B has the same employee numbers but has addi...

Can I retrieve deleted email from the enlarged database
Version: v.X Operating System: Mac OS X 10.4 (Tiger) Processor: Power PC Email Client: pop My v.X database is 950Mb with very little email. I know this is because of the deleted emails of the past and that rebuilding the database would produce a new database the right size. I would like to know if I can get to the deleted emails. Can I get them back. I have run in to an issue that I need them. <FONT FACE="Calibri, Verdana, Helvetica, Arial"><SPAN STYLE='font-size:12pt'>On 5/11/10 11:41 PM, in article <a href="59bb859f.-1@webcrossing.JaKIaxP2ac0&q...

Can MsPubliser do INCLUDETEXT or MasterDocument/SubDocument equiv?
I'm preparing a series of bifold + duplexed booklets. There are 8 page, 16 page, 24 page, and 40 page versions with much duplicated text. I've used Word's Master-Document with sub-documents (and also the INCLUDETEXT fieldcode) so that changes / typos only have to be fixed in one file. I'd like to use the same MasterDocument approach with Microsoft Publisher 2003. Is this capability available with Publisher? This would be for duplexed quad-fold versions that end up being 4.25"x5.5". Does Publisher 2003 have the feature of a master document being able to include separ...

Publisher calendar, how can I show the previous/next month?
I need to show the past and previous months in blank days at the top of a per month calendar, but can't see how to do that. Thanks. Create a yearly calendar in a new publication. Ungroup, copy the separate months, paste to your main calendar. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Jennie" <Jennie@discussions.microsoft.com> wrote in message news:AF4CF698-ABB8-4F01-A667-CC6AA87A9732@microsoft.com... >I need to show the past and previous months in blank days at the top of a per > month calendar, but...

Enable macro #4
Is there a way that when a user opens an excel workbook that it will enable the macro without asking to disable or enable? I have users that click on disable, then the macro will not run. I have two macro buttons set up and they choose the one that is needed at this time. I would rather it open and the enable already be set without them having to click on it. Is this possible? No, Pam. Sorry. You can "force" them to enable. http://vbaexpress.com/kb/getarticle.php?kb_id=578 So, you could set it up that if they don't enable, they don't see... if they do enable, no...

Can I run ON from an external drive without it being installed on
Can I run ON from an external drive without it being installed on a local machine? "Ang130" <Ang130@discussions.microsoft.com> wrote in message news:5982603B-D3AC-48F3-84A4-D96DB4696889@microsoft.com... > Can I run ON from an external drive without it being installed on a local > machine? With Ceedo software Ang130 wrote: > Can I run ON from an external drive without it being installed on a > local machine? YES, if run under Ceedo with the Argo installer.[1] In theory it should also work with a USB stick. It would have a superfast stick, m...

How do I create a "floating" header in Excel so I can always see i
contact me at andy@boydrealestateinc.com Andy To have row 1 always in view as you scroll down. Select A2 and Window>Freeze Panes. Rule is: whatever is above and left of the selected cell will be frozen after you hit Window>Freeze Panes. i.e. select B2 to have Column A and Row 1 frozen. Gord Dibben Excel MVP On Wed, 16 Nov 2005 13:38:00 -0800, Andy Heal <Andy Heal@discussions.microsoft.com> wrote: >contact me at andy@boydrealestateinc.com ...

Cell can left indent; anything for right side of cell?
Is there a way to leave more space between the end of a word and the right side of the column? I indented the left side of the column by using the indent option set to "1" in the alignment tab of the formatting box. Besides adding an extra column to the end of the row and making it very small in width and extending the border to that extra "spacer" column, is there a way to make this space? My spreadsheet is done and if I have to do this, I'll have tons of work re-writing code. I was going to use Word for the final presentation of the data, but it didn't work ou...

Can't Email to Moved Domain
We had a sister company operating within our WAN. We hosted their mail on our single Exch 2003 server. This company was sold and moved off of our servers. We removed all of their information from our Exchange server. I have deleted all of their mailboxes, AD user accounts, any reference to their domain in the recipient policies, and any entries in our dns system for their domain name. Still, when we try to send messages to these people at their new location we get an NDR that says "The e-mail account does not exist at the organization this message was sent to. Check the e-mail addr...

Can I ask WCF questions here?
I am just wondering if this is where I can post a Question about WCF of if there is a different newsgroup I should use? Thanks, Jeff ok Thanks i will try there Jeff "DickGrier" <dick_grierNOSPAM@msn.com> wrote in message news:%235bp4jiZKHA.1652@TK2MSFTNGP05.phx.gbl... > Hi, > > Probably microsoft.public.donet.webservices. > > You also might ask here, though I suspect that more detailed responses may > be found above. > > Dick > > -- > Richard Grier, Consultant, Hard & Software 12962 West Louisiana Avenue ...

How can I set margins for headers on charts. The margin option ap.
I need indent chart headers to coincide with margins selected fopr the chart itself. I can not find any control in Excel or printer softeare that will indent a left-justified header. As you noted, there is no built-in chart option to indent a left justified header. I would suggest replacing the header with a text box. You can then use character spacing within the text box to create the look of an indented header. ---- Regards, John Mansfield http://www.pdbook.com "sayles1986" wrote: > I need indent chart headers to coincide with margins selected fopr the chart >...

Can't Select Addresses for "Send Email Statements" Function
When I try to select an email address from my Outlook address book on the "send email statements" area on the customer maintenance options window, the email does not display. I get the dialog box to select the email from my address book, but after clicking ok, the address does not display in the appropriate field (I've tried TO:, CC:, and BCC:). I'm using Windows 7 pro 64 bit and Outlook 2007. Any ideas? More info on this. Other users who are running Windows XP and Outlook 2003 can select entries from their address book and have them show up in the appropr...

can a program send email on users behalf with out getting the nag boxes?
I am trying to allow a program to send an e-mail through outlook 2003 on my behalf without getting the nag boxes. We have a customized program that sends out emails for us but since we moved to outlook this nag box pops up for each one. We have to sit there for hours clicking yes to each one and allowing access for 10 min maximum. I have installed the outlook security features administrative package on my machine and followed the read me to configure it to allow this but have not been successful. This is what I have tried: 1. The admpack.exe installed easily then I installed a...

Can't separate formula parameters with a comma
I have a very weird problem. For some reason I cannot call any functions that require more than one parameter, because office doesn't like the comma between the parameters. Even though a comma is what you must use between parameters. The tooltip also comes up with a space between parameters instead of a comma. The error message is: Microsoft Office Excel found an error in the formula you entered. I desparately need help with this. I have reset the user profile to the default settings using the profile wizard, and I have even tried reinstalling Office. Nothing has worked. Please help...

how can i make a web site scale-able?
I want my site to look the same on all resolutions, how can I do this? I responded to this in the correct forum of .webdesign One post is sufficient please. -- David Bartosik - MS MVP for Publisher help: www.davidbartosik.com enter to win Pub 2003: www.davidbartosik.com/giveaway.aspx "J" <joshkligman@hotmail.com> wrote in message news:1ecd01c3e04a$165cd450$a501280a@phx.gbl... > I want my site to look the same on all resolutions, how > can I do this? ...

Can't delete deposit from budget
I deleted a paycheck from my bill & deposits. The deposit still shows up when I run budget reports for the months originally covered by the deleted deposit (no future months). When I look at the calander tab within Bills & deposits it still shows up but with a line through it. I just want to remove it entirely from my budget. Any thoughts?? Thanks ...

Can you filter or sort by a cell's formatting? #2
Can you filter or sort by the formatting of a cell - these were formatted manually and do not have conditional formatting applied to them - thanks You can even use a UserDefined Function that can return the colorindex. Chip Pearson has a nice function at: http://www.cpearson.com/excel/colors.htm Returning The ColorIndex Of A Cell If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm MalibuinthePeg wrote: > > Can you filter or sort by the formatting of a cell - these were formatted > manually and do...

Pull back funds from the bank
An Employee got paid 2 disbursements (March 20th and April 3rd) of TEMP (Temp Pay Code of Hourly)(this is the only pay code they have … they do not do anything else for the employer) into their bank account (via Direct Deposit). The payroll department was just notified that the employee never actually worked and should not of gotten paid. How do you pull the 2 disbursements back? You cannot "pull back funds from the bank" because the money already made it to the employee's bank account. You will have to ask the employee (ex-employee in this case) to voluntarily r...

Can't install Server 2008 Service pack 2 (x64)
i keep getting error code 800F0826 with no other info. trying to install on my sbs 2008 premium box. any suggestions?? This is a multi-part message in MIME format. ------=_NextPart_000_0048_01CA8ED5.5AF75FA0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Are you using the Microsoft tool to make this installation possible? http://www.microsoft.com/downloads/details.aspx?displaylang=3Den&FamilyID= =3Dffd2fe61-9278-489e-9b96-3816394c9cb6 "BadBoy House" <mrchrishouse@googlemail.com> wrote in mess...