طریقه جدا کردن عدد از متن در اکسل

در این مطلب نحوه تجزیه متن در اکسل بصورت عمومی اشاره شده است. برای یک حالت خاص، در یک سلول شامل ترکیبی از اعداد و حروف، چنانچه بخواهیم آن‌ها را از هم جدا کنیم همیشه نمی‌توان از روش‌های عمومی استفاده کرد. در ادامه مطلب با ۱۴ خورشید همراه باشید با نحوه این جداسازی آشنا شوید.

برای اینکار دو حالت وجود دارد:

حالت اول) در همه سلول‌ها عدد یا حروف در یک جهت قرار داشته باشد مثلا در تمام سلول‌ها عدد در سمت راست باشد و اینکه محتویات سلول‌ها مثل ۱ش۲س بصورت ترکیبی نباشند. البته باید یکی از شرایط زیر را نیز دارا باشد:

۱- بین عدد و حروف، کاراکتر جداکننده خاصی مثل فاصله یا ویرگول یا ممیز یا … قرار داشته باشد. البته این کاراکتر جداکننده باید در تمام سلول‌ها یکسان باشد. مشابه این مورد در حالت ۱ این مطلب ارائه شده است.

۲- یا اینکه یکی از دو مورد عدد یا حروف درون تمام سلول‌ها تعداد کاراکتر یکسانی داشته باشد.

مثال ۱) در شکل زیر در همه سلول‌ها، اعداد در سمت چپ قرار دارند و ۴ رقمی هستند.

فرمول‌های زیر را به ترتیب در سلول B1 و C1 کپی کنید:

=LEFT(A1,4)

=MID(A1,5,LEN(A1)-4)

نحوه استفاده از توابع LEFT ،MID و LEN قبلا در اینجا ارائه شده است. پس از اعمال فرمول‌ها برای سلول‌های A1 تا A4، نتیجه زیر حاصل می‌شود.

مثال ۲) در شکل زیر در همه سلول‌ها، اعداد در سمت راست قرار دارند و ۴ رقمی هستند.

فرمول‌های زیر را به ترتیب در سلول B1 و C1 کپی کنید:

=RIGHT(A1,4)

=MID(A1,1,LEN(A1)-4)

نحوه استفاده از توابع RIGHT ،MID و LEN قبلا در اینجا ارائه شده است. پس از اعمال فرمول‌ها برای سلول‌های A1 تا A4، نتیجه زیر حاصل می‌شود.

حالت دوم) در حالتی مثل شکل زیر که تعداد کاراکترهای عدد و حروف درون سلول‌ها متفاوت و بصورت ترکیبی می‌باشند و همچنین فاصله بین متن و عدد در سلول‌ها متفاوت است، دیگر نمی‌توان از روش‌های حالت ۱ (بالا) استفاده کرد.

برای این حالت باید از روش‌های زیر استفاده نمود.

۱- با استفاده از فرمول نویسی

بوسیله فرمول زیر می‌توان عدد را از ترکیب جدا کرد.

=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$25),1))*ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)

نتیجه آن در شکل زیر قابل مشاهده است.

۲- با استفاده از ماکرو

• استخراج عدد از ترکیب

کلیدهای Alt + F11 را فشار دهید یا از تب Developer قسمت code گزینه Visual Basic را انتخاب کنید. پنجره Microsoft Visual Basic ظاهر می‌شود. در این پنجره از تب Insert گزینه Module را انتخاب نمائید.

در پنجره جدید باز شده کدهای زیر را کپی کنید.

Sub GetNumbers()
'pctarfand.ir & tarfandha.blog.ir'
Dim Rng As Range
Dim WorkRng As Range
Dim xValue As String
Dim OutValue As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    OutValue = ""
    xValue = Rng.Value
    For i = 1 To VBA.Len(xValue)
        If VBA.IsNumeric(VBA.Mid(xValue, i, 1)) Then
            OutValue = OutValue & VBA.Mid(xValue, i, 1)
        End If
    Next
    Rng.Value = OutValue
Next
WorkRng.NumberFormat = "@"
End Sub

محدوده مورد نظرتان را انتخاب کنید و روی Ok کلیک کنید تا نتیجه زیر حاصل شود.

با تعریف یک تابع نیز می‌توان این جداسازی را انجام داد که در اینجا ارائه شده است.

• استخراج حروف از ترکیب

بجای کد بالا از کد زیر استفاده کنید.

Function TextOnly(pWorkRng As Range) As String
'pctarfand.ir & tarfandha.blog.ir'
Dim xValue As String
Dim OutValue As String
xValue = pWorkRng.Value
For xIndex = 1 To VBA.Len(xValue)
    If Not VBA.IsNumeric(VBA.Mid(xValue, xIndex, 1)) Then
        OutValue = OutValue & VBA.Mid(xValue, xIndex, 1)
    End If
Next
TextOnly = OutValue
End Function

=TextOnly(A1)

۳- با استفاده از افزونه Kutools for Excel:

نحوه استفاده از افزونه Kutools for Excel برای استخراج عدد و استخراج حروف در اینجا و اینجا مشاهده کنید.