Extracting a character from a string of characters

I would like to extract the 6th character (the 5) from the following example 
of a string of characters e.g.B123456789.
 

0
sue (191)
10/29/2005 10:01:01 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
555 Views

Similar Articles

[PageSpeed] 4

Have a look in HELP index for  MID

-- 
Don Guillett
SalesAid Software
donaldb@281.com
"Sue" <Sue@discussions.microsoft.com> wrote in message
news:73FB02F4-EDA0-4819-9280-3F74F4163B4E@microsoft.com...
> I would like to extract the 6th character (the 5) from the following
example
> of a string of characters e.g.B123456789.
>
>


0
Don
10/29/2005 10:05:05 PM
If its always the 6th character use MID
i.e. if B123456789 was in cell A1

=MID(a1,6,1)


-- 

_______________________
Naz,
London


"Sue" wrote:

> I would like to extract the 6th character (the 5) from the following example 
> of a string of characters e.g.B123456789.
>  
> 
0
Naz (61)
10/29/2005 10:19:03 PM
To be different, try this:

=RIGHT(LEFT(A1,6))

OR,
To be conventional:

=MID(A1,6,1)
-- 

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


"Sue" <Sue@discussions.microsoft.com> wrote in message
news:73FB02F4-EDA0-4819-9280-3F74F4163B4E@microsoft.com...
I would like to extract the 6th character (the 5) from the following example
of a string of characters e.g.B123456789.



0
ragdyer1 (4060)
10/29/2005 10:21:09 PM
=mid(The String,6,1)


-- 
Pan
------------------------------------------------------------------------
Pan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28144
View this thread: http://www.excelforum.com/showthread.php?threadid=480398

0
10/29/2005 10:30:03 PM
IN addition to the formula answers given, you can also do this by using the
data > TextToColumns feature........and set the column breakes where you
wish, then, if desired, you can later concatenate the string back together
without the old 6th character.......or change it if desired.

Vaya con Dios,
Chuck, CABGx3


"Sue" <Sue@discussions.microsoft.com> wrote in message
news:73FB02F4-EDA0-4819-9280-3F74F4163B4E@microsoft.com...
> I would like to extract the 6th character (the 5) from the following
example
> of a string of characters e.g.B123456789.
>
>


0
croberts (1377)
10/29/2005 10:50:12 PM
This will allow you to change the 6th character to whatever value is in cell
F1............

=LEFT(A1,5)&F1&MID(A1,7,99)

Vaya con Dios,
Chuck, CABGx3



"Sue" <Sue@discussions.microsoft.com> wrote in message
news:73FB02F4-EDA0-4819-9280-3F74F4163B4E@microsoft.com...
> I would like to extract the 6th character (the 5) from the following
example
> of a string of characters e.g.B123456789.
>
>


0
croberts (1377)
10/30/2005 12:35:50 AM
Reply:

Similar Artilces:

Extracting first letter of last name in name field
I have a name field that has their first and last names. I need to extract the first letter of their last name, is there an easy way of doing this? thanks, Kim P -- Message posted via http://www.accessmonster.com Left([Surname], 1) -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!) "klp via AccessMonster.com" <u19032@uwe> wrote in message news:a613753bfef90@uwe... >I have a name field that has their first and last names. I need to extract > the first letter of their last name, is there an easy way of d...

How to print 2000+ characters seen in formula bar?
I have more than 1024 characters in one cell. I can see the complete text in the formula bar but I cant print it Hi enter some manual linebrekas with ALT+ENTER every 1000 characters -- Regards Frank Kabel Frankfurt, Germany "Excel 2003" <Excel 2003@discussions.microsoft.com> schrieb im Newsbeitrag news:BEDAFD71-FA65-4500-A94D-90F4AAB57BEA@microsoft.com... > I have more than 1024 characters in one cell. I can see the complete text in > the formula bar but I cant print it Actually a cell can only display a maximum of about 1000 characters. It's just a limitati...

Trying to get an ASCII string.
Hello, I have an application that extracts ASCII strings from a binary file (characters from 0 to 255). Depending on the windows code page of the user, the characters above 127 do not "mean" the same : that is normal. My problem now consists in saving that string in text format while keeping every character on a BYTE and not .Net CHAR Example : Let's say a russian guy uses my program, the tool extracts C0 F0 E3 E5 ED F2 00. (Assuming buffer contains the binary data, this is how I extract the string : ) StringBuilder sb = new StringBuilder(); ...

simple types and string type
Hi! For example you have value types and reference types. If we say that an int is a simple type what would we say about the string type except that it is a reference type ? //Tony Tony Johansson wrote: > Hi! > > For example you have value types and reference types. > If we say that an int is a simple type what would we say about the string > type except that it is a reference type ? It's an immutable reference type. "Harlan Messinger" <hmessinger.removethis@comcast.net> skrev i meddelandet news:7s5i1jFitbU1@mid.individual.net...

Extracting CC Email Address Using VBA
Hello, I have the current code that works for extracting various information from the MailItem Object: If itm.Class = olMail Then Set msg = itm objWks.Cells(i + 1, 1) = msg.Subject objWks.Cells(i + 1, 2) = msg.ReceivedTime objWks.Cells(i + 1, 3) = msg.SenderName objWks.Cells(i + 1, 4) = msg.SenderEmailAddress objWks.Cells(i + 1, 5) = msg.Body objWks.Cells(i + 1, 21) = msg.CC objWks.Cells(i + 1, 22) = msg.To It is dumpting the data into a...

Undo only by single character in Word 2007?
In Word 2007 under Windows 7, I am noticing for the first time a strange behavior. I know in Word 2002 and OSs up through Vista, undo worked on whole words. Now I find in certain files, that undo works only character by character, not whole words. The drop down list for undo shows, for example typing "e." I notice that in opening a new file, undo works as expected. Ditto for other existing files. The current file for which this behavior occurs was sent to me by a prospective author. The main document at issue is a DOCX file, presumably originated in Word 2007 (or...

Can I have more than 255 characters in Chart "Values" of Excel 2k?
Basically put, when I'm trying to graph specific values from a very large and complex spreadsheet, I can't use all the cells I need to use because the X Values: and Y Values: are truncated at 255 characters. Is there any sort of fix for this? Excel has a specification limit of 255 data series in a chart Not sure I could read with much more than 10! -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Lloyd Benson" <Lloyd Benson@discussions.microsoft.com> wrote in message news:4E197374-9397-4B23-9F3C-9D79F75E04FB@microsoft.com... > Basicall...

Extracting Intials from a name in a cell?
Hi all, Is there an easy way to extract JUST the initials of a name in a cell if the name format is: Cell A1 = Bob Smith need Cell B1 = BS Thanks Brian If thge name is like firstname space lastname however with double names and initials and otyer attributes it becomes complicated, but for your example =LEFT(TRIM(A1))&MID(TRIM(A1),FIND(" ",TRIM(A1))+1,1) will do Regards, Peo Sjoblom "Brian" wrote: > Hi all, > > Is there an easy way to extract JUST the initials of a name in a cell if the > name format is: > > Cell A1 = Bob Smith &g...

Extract embedded file from table to folder
I have an table in access with embedded excel files I want to extract them to a folder with automation - the only way I know is to create af Form, and then automatically open and close the form. In the on-open event I run the following code: Dim oXL As Object 'Dim strFileSpec As String 'Activate the embedded document in an instance of Excel 'MyOle is a bound object on the form Me!MyOle.Action = acOLEActivate 'get hold of the instance - assumes that there is only the one 'Instance of Excel running Set oXL = GetObject(, "Excel.Application") 'Save the docu...

COleDateTime parsing string
I have to parse date/time strings that are available in this format: 2005-10-13T07:00:00Z (represents 7am on Oct 13) 2005-10-13T014:00:00Z (represents 2pm on Oct 13) The user's locale can be anything (US, UK etc) When I call COleDateTime::ParseDateTime, it fails with the default LCID. Does anyone know which LCID I should use to parse date/times is the above format? Thanks! Take a look at the GetUserDefaultLCID() API. -- Cheers Check Abdoul ----------------- "Mel Johnson" <test@test.com> wrote in message news:uAYYMHC0FHA.2752@TK2MSFTNGP12.phx.gbl... &g...

Cell truncated to 256 characters during copy to new worksheet
When you copy a cell which has greater than 256 characters from one worksheet to another the cell gets truncated to 256 characters. I have used the workaround suggested in the MS knowledge base. Does anyone else have any ideas? Brad Was the suggestion in the KB to copy the worksheet, then come back and copy the cells, then paste the cells? If it was, then I don't have another suggestion. If it wasn't, then I do. Brad Backlin wrote: > > When you copy a cell which has greater than 256 characters > from one worksheet to another the cell gets truncated to > 256 char...

Extract specific string
Hi all, I need extract a specific string and my cells may contains many alphanumeric data In this example I need Extract only S00059997 and S00075671 the LEN is 9 all the time. Thanks for your Help Data_Account ---------------- ANY-NOTES ANY---NAMES S00059997 ANY-NOTES S00075671 Dante I think you may have to tell us more about what you are trying to do and what you have to work with. First, I'm assuming each item in the list you showed is on a separate row. Do the strings you are trying to extract always start with a single letter? If yes, I'm guessing it i...

Cell Character Limitation???
Does anyone know if there is a limit on the number of characters (text and/or numbers) that will show in a cell? I have a worksheet that has long-winded email text pasted into a cell, and no matter what I do to change the width, the text doesn't want to wrap after about 400 characters. 256 i think -- Don Guillett SalesAid Software donaldb@281.com "Laura Brown" <anonymous@discussions.microsoft.com> wrote in message news:843FE413-16BA-45DC-84F0-8886DB54BB87@microsoft.com... > Does anyone know if there is a limit on the number of characters (text and/or numbers) that wi...

Extracting Text from a Cell
I have a column in excell that has 1-4 names in each cell. I would like to extract the names into 4 seperate cells (column). The names are seperated by a comma and a soft return. I'd appreciate any help. Look at Data>Text To Columns -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Tony" <anonymous@discussions.microsoft.com> wrote in message news:2031101c4588f$bfe1f240$a001280a@phx.gbl... > I have a column in excell that has 1-4 names in each cell. > I would like to extra...

Outlook 2003
Hi! I am managing all of my contacts in Outlook 2003, and added pictures (photos) of each contact. So far okay. Now i want to export my contacts (as vcards) - no problem, but where are the pictures? Can anybody help me? Where can i find the pictures? Are they stored in my .pst file? How can i extract them? Thanks very much! ehrengruber@gmail.com <ehrengruber@gmail.com> wrote: > I am managing all of my contacts in Outlook 2003, and added pictures > (photos) of each contact. So far okay. Now i want to export my > contacts (as vcards) - no problem, but where are the pictures?...

ComboBox strings don't show up
I am creating a combo box at run-time. It shows up on my dialog, but when I click on the down arrow, the drop-down list doesn't appear. If I click in the box itself, then hit the down arrow KEY, the strings I've added do appear...just nothing appears when I click the down arrow adjacent to the box. Any ideas? Thanks. Never mind. The rectangle I was using in the Create() call wasn't tall enough. "time4u2go" wrote: > I am creating a combo box at run-time. It shows up on my dialog, but when I > click on the down arrow, the drop-down list doesn't appea...

Please assist. Extract Number from String
How can I use a macro to extract only the first batch of numbers from the following type of info? Examples: 01458-MODE 1548-JUNE 1245-NOD 01054-MORNING 00154-JUNE 55145-55145 Result should be: 01458 1548 1245 01054 00154 55145 -------------------- I only need the first batch of numbers before the "-" sign. There will always be this sign "-" separating the two batch of data. Thanks! firstBit =3D Split(theValue,"-")(0) Tim On Dec 16, 8:39=A0am, Damil4real <damil4r...@gmail.com> wrote: > How can I use a macro to extr...

Swedish characters
We have problem with outgoing e-mail for users who have Swedish characters (���) in their name. It is not possible to reply to the e-mail they send out (from the organization), only their last name is set as their reply address. An example: Karlsson, J�rgen sends an e-mail to someone outside of our organization. When that person tries to reply to his e-mail, the mail is sent to "Karlsson". His SMTP server, of course, gives him a non delivery report. Any ideas? A simple setting somewhere? ...

Including double-quotes in strings
I want to use the substitute function to replace commas "," with double-quotes. Is there a way to specify double quotes in strings? Hi! =SUBSTITUTE(A1,",","""") Will replace all commas with " Biff "Hall" <hall@garp.org> wrote in message news:OhjSQk6oFHA.3304@tk2msftngp13.phx.gbl... >I want to use the substitute function to replace commas "," with > double-quotes. > > Is there a way to specify double quotes in strings? > > yeah. how about this. keep your data in a database and then when you ne...

Allow Numeric Characters Only In A Cell
Hello! I am wanting to both limit a cell to numeric entry only and require 12-digits. Example of typical entry: 01-2005-12345. I have to ensure that the zero will show up at the start if that is what they need to enter and the dashes must fall in the string as shown. I do not want the user to have to key the dashes. I currently have been able to use "Format Cells" to ensure the zero shows up and the dashes automatically insert into the string by using custom format and 0#-####-##### , but I am not able to require 12-digits and limit to numeric entry only. Any help would be g...

Extracting data from workbooks. Help.
hello I have a situation where there are several workbooks, of variable name, contained within a folder on my hard drive. I have a master workbook in a folder higher up the same directory tree that I want to use to gather information from the other workbooks of variable names. The cell, A1, that I am interested in is on sheet "OnCall" in each of the workbooks of variable name. Is there any way that I can open the master workbook and then automatically extract the data from cell A1 in each of the other variable name workbooks? I hope that as clearer than mud? hopefully Tom Here&...

Convert a textbox string value into date
I need to convert a textbox string value in the form of 31-12-09 into a date in the same format, can any one help please, thank you. >>I need to convert a textbox string value in the form of 31-12-09 into a date in the same format, Converting I understand but 'into a date in the same format' is confusing. DateSerial("20" & Right("31-12-09",2),Mid("31-12-09",InStr("31-12-09","-")+1,2),Left("31-12-09",2)) If you format it for display the results is a string. If you are going to use it in calculati...

retrieving connection string w/ ConfigurationManager
I am trying to store and retrieve connection string info from config file and I am having trouble retrieving the connection string info. I stored the info with the following: Configuration config = null; config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None ); config.ConnectionStrings.ConnectionStrings.Add( new ConnectionStringSettings( "MyConfigurationString", "Server=local;Database=Test;Password=password;User Id=john;" ) ); config.Save(); I then remove the Add method and use the following try to retrie...

VBScript String Clean function
This article provides a function written in VBScript which removes illegal characters from a string. The illegal characters are specified in an array at the beginning of the function. The function is passed the string which may contain illegal characters, which then processes the string to remove or replace any illegal characters. The code also allows you to specify the character or word which will replace specific illegal characters. The illegal characters provided in the function are included for processing strings which will become the filename of a document or list item in Sh...

Foreign language characters
Long ago I set up for EN, DE, HU, and FR with the access from toolbar. Haven't used them in a while, and they don't seem to work now....the umlaut etcetera just aren't there. Is there any way I can reload or download these? William B. Lurie wrote: > Long ago I set up for EN, DE, HU, and FR with the > access from toolbar. Haven't used them in a while, > and they don't seem to work now....the umlaut etcetera > just aren't there. Is there any way I can reload or > download these? the umlaut etcetera - que? perhaps this may help Control Pane...