CODEWITHSUNDEEP
excelvba
CarbonD1225
CarbonD1225

Reputation: 869

how to trim data in a cell in Excel 2007 VBA where there is a symbol

I want to trim the data inside the cell so that i have eveything before the hyphen

For example, if a cell contains: 

'Net Operating Loss - All Pre-1990 Carryforwards'

i want to trim it in such a way that i only have: 

'Net Operating Loss'

Any suggestions on how to do this

Upvotes: 0

Views: 450

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149287

Like this?

Option Explicit

Sub Sample()
    Dim strSample As String

    strSample = "Net Operating Loss - All Pre-1990 Carryforwards"

    Debug.Print Split(strSample, "-")(0)
End Sub

BTW Do you need to do this in VBA. The above can be achieved using Excel Formulas as well.

Let's say you have "Net Operating Loss - All Pre-1990 Carryforwards" in Cell A1 then put this in Cell B1

EXCEL 2003

=IF(ISERROR(MID(A1,1,FIND("-",A1,1)-1)),"",MID(A1,1,FIND("-",A1,1)-1))

EXCEL 2007 Onwards

=IFERROR(MID(A1,1,FIND("-",A1,1)-1),"")

Upvotes: 3

Lakedaimon
Lakedaimon

Reputation: 1934

Hy,

you use InStr to search for the hyphen and use the resulting position as length for the Right Command.

dim p as integer
p = InStr(YourStr, '-');
Trim(Right(YourStr, p - 1))

A little reference is here http://www.techonthenet.com/access/functions/index.php

Upvotes: 0

Related Questions