extract specified text from column (IE 5.5)

[indro]

I have one column data that I want to extract specified abbrevation - which is listed - in to separate column so I can easily group the other column. Is there way to do automatically?

regards,

Indra

[sdckapr]

You can create a lookupof possibilities with their abbreviation.

many seemed to be able to be gotten with:
=MID(A2,2,4)

copied down a column, but there are some that do not work

=IF(ISNUMBER(MATCH(MID(A2,2,4),'list abbrevation'!$A$3:$A$8,0)),MID(A2,2,4),"???")

copied down will guess at the abbreviation and if it is in the list, it will display it otherwise it will be "???" and you can manually just check on those

Steve

[indro]

I've tried that before, but I have lot of data and still look for faster way.
Thanks anyway steve,

regards
indra

[HansV]

If the data don't change very often, you could use a macro to fill a column with the abbreviations.
But you'd have to remember to run the macro each time the data change.

[sdckapr]

<P ID="edit" class=small>(Edited by sdckapr on 24-May-06 12:18. Added PS)</P>You can use a custom function.

Add this to a module in the workbook:
<pre>Option Explicit
Function GetAbbr(sDesc As String, rAbbr As Range)
Dim rCell As Range
For Each rCell In rAbbr
If InStr(sDesc, rCell.Value) <> 0 Then
GetAbbr = rCell.Value
Set rCell = Nothing
Exit Function
End If
Next
GetAbbr = CVErr(xlErrNA)
Set rCell = Nothing
End Function</pre>


Then you can enter in C2 (expand the range of abbreviations as desired)
<pre>=GetAbbr(A2,'list abbrevation'!$A$3:$A$8)</pre>


And copy down column C. The function goes thru each of the values in the "abbreviation range" and sees if it is in the string. The first one found is considered the abbreviation and the function stops looking any more.. If none are found a #NA is given (change to something else if desired).

Steve

PS if you want the search to be non-case sensitive use the line:
<pre> If InStr(UCase(sDesc), UCase(rCell.Value)) <> 0 Then</pre>

[indro]

this is exactly what I've been looking for!! i'm really grateful
thank you very much steve, thank you for the forum

regards,

indra

[indro]

Hi guys, it's me again.
where i can learn macro/vba like you did to my problem above, or any modul/website/book you could give me for reference?
I have enough in excel function, but in some case it not support anymore
I've tried (not hard enough I think,) to read some book and browse some material but my ability stuck in the level such as copy paste or only insert vba modul : )
I am an accountant for not profit organisation, and my objective is to learn any vba stuff relate my job.

Thanks again

indra

[HansV]

See <post:=539,691>post 539,691</post:> for some book recommendations.

How to use Visual Basic for Applications in Excel contains links to Microsoft articles about Excel VBA.

If you search Google for excel vba tutorial you'll find many online tutorials.