PDA

View Full Version : Solved: Extract number after text from each cell of a column



nvnazeer
01-12-2008, 08:39 AM
I have a Spreadsheet which has one column populated with lots of information under the header "Description". I would appreciate if someone can help me to extract the 11 digit number that is next to the text "CMN Part No:" into a seperate column next to the "Description" column. This is because the spreadsheet is quite big with more than 2000 rows.
I am totally unfamiliar with VBA and hence would appreciate help from forum members.
If someone can tell me how to attach the spreadsheet I can attach the spreadsheet as well.

Thank you for your response

Nazeer

Bob Phillips
01-12-2008, 10:08 AM
Just us

=MID(A1,11,1)

in that column, and copy down. No need for VBA.

nvnazeer
01-12-2008, 01:01 PM
Thanks for the reply. But that does not work. This is because the location of the Text CMN Part No: is different in each cell. Therefore the extraction is to be based on the 11 digit number that occurs in the cell immediately after the text "CMN Part No:" The text CMN Part No. may be positioned anywhere within the cell.
Please help.

Thanks

Nazeer N.V

Bob Phillips
01-12-2008, 01:56 PM
Give an example.

Aussiebear
01-12-2008, 11:58 PM
G'day nvnazeer, As I understand it, you need to complete at least 5 posts to the forum, before you can attach a file. Mind you I might be wrong about this...( It wouldn't be the first time either).

Then you use "Go Advanced" to manage your attachments. In the mean time be patient, give as much info mation as you possibly can, and someone will help you.

nvnazeer
01-13-2008, 01:12 AM
Thank you Aussie bear.
I was wondewring why iam not able to send attachment.

I will post the attachment as soon as Iam able to.

Regards

Nazeer

Bob Phillips
01-13-2008, 03:34 AM
Just post a **bump** message, that will be 4, then you can post an attachment in number 5.

nvnazeer
01-13-2008, 09:28 AM
Hi
Thanks for the continous support.

Bye

nvnazeer
01-13-2008, 09:36 AM
Still no luck.

Thanks

Carl A
01-13-2008, 10:08 AM
This will find the colon and return the specified number of characters to the right. That is if only one colon is in the Description.

=MID(A1,FIND(":",A1)+1,12)
12 if there is a leading space 11 if not.

rory
01-13-2008, 04:12 PM
You could just search for the exact text:
=MID(A1,SEARCH("CMN Part No:",A1)+12,11)
adjust if necessary for any spaces in between the text and the start of the number.

nvnazeer
01-14-2008, 12:40 PM
Hi,
Iam attaching a small extract from my spreadsheet. I have shown in red the extraction as an example for rows 2 and 3. The same needs to be done for each row. As mentioned earlier the 11 digit number that occurs in the cell immediately after the text "CMN Part No:" to be extracted.

Thanks in advance

nvnazeer
01-14-2008, 12:41 PM
Hi,
Please also note that the location of the CMN No: is also not the same in all cells.

Thank you

Nazeer

Bob Phillips
01-14-2008, 12:48 PM
=MID(C2,FIND("CMN Part No: ",C2)+LEN("CMN Part No: "),FIND("CMN Part No: ",C2)+LEN("CMN Part No: ")-FIND("CMN Part No: ",C2)-2)

nvnazeer
01-14-2008, 01:00 PM
Thank you very much!
It works like a Charm. Wonder whether you have any reference material so that I can learn to write such formulas myself.

Nazeer N.V

Bob Phillips
01-14-2008, 01:40 PM
Not really, sorry. It is gleaned from experience, and many references over the years.

rlv
01-14-2008, 09:48 PM
As an alternative, you could also use a user defined function (UDF) to extract the number

Function ExtractCMN(CellText As String) As String

Const SearchText = "CMN Part No:"

Dim SPos As Integer, SLen As Integer
Dim CMN As String

SLen = Len(SearchText)
SPos = InStr(Trim(CellText), SearchText)

If SPos > 0 Then
CMN = Trim(Mid(CellText, SPos + SLen, 12))
Else
CMN = "Not Found"
End If
ExtractCMN = CMN
End Function

nvnazeer
01-15-2008, 10:46 AM
Gents,
I need a little more help with my Spreadsheet. I have attached the spreadsheet.
The Original spreadsheet is as shown in sheet1. For all items that have same the same "CMN Part No"., I want to automatically add the corresponding values under quantity column and replace this value with the added quantity on the first row and then delete the rows below(having the same "CMN Part No.') for which the quantities are now added to already in the first row.
This needs to be continued whenever the CMN part No. is the same for adjacent rows.
The end result required is shown on Sheet2.
Since my spread sheet is quite big and has several rows which are with unique "CMN part No." and others with same "CMN part No:" repeated. The program should skip the ones which are unique and then addup the quantities where the CMN part No. is same and delete rows whose quantites have been added. Of course the spreadsheet will be sorted by "CMN Part No." so that the rows with the same CMN part No. are together.

Thank you in advance for the help

Nazeer N.V